Lesson 3: Entering Functions

Lesson 3: Entering Functions

Use Insert Function to Use a Function

There are many functions to use to speed up calculation of your data. To create other functions, you can type them in a cell or use the Insert Function dialog box.

To use a function with the Insert Function, do the following:

1. Select the cell that will contain the function.

2. Do one of the following to display the Insert Function dialog box.

   • Click to insert a function.

   • Click Insert, and then choose Function.

   • Press Shift+F3.

3. Choose a function category and a function name. The Most Recently Used Category allows you to choose from functions you use often.

   To look for a function, type the function name in the Search a Function text box and then click Go.

   If you need help on a function, select the function and then click the Help on this function link.

4. Click OK. The Formula Palette opens.

   The Formula Palette displays the arguments that the function needs.

5. Fill each argument using any of the following ways:

   

   • Drag the cell if it is visible on the screen.

   • Type a cell address or cell range.

   • Click the Collapse Dialog button; select the range on the worksheet. When you are finished, click the Redisplay Dialog button.

6. Repeat Step 5 for any arguments you are using, and choose OK when finished.

Entering Functions using the Formula Palette

Aside from using the Insert Function dialog box to enter the formula palette, you can also enter it directly through a shortcut key and the most commonly used function drop-down list on the formula bar.

To enter a function through the Formula Palette, do the following:

1. Select the cell where you want the function to appear.

2. Type = equal sign.

3. Click the drop-down list on the left edge of the Formula bar.

   

4. Choose one function on the list or select More Functions to go to the Insert Function dialog box.

5. The Formula Palette opens, displaying the arguments for the function.

   

   Fill each argument using any of the following ways:

   • Drag the cell if it is visible on the screen.

   • Type a cell address or cell range.

   • Click the Collapse Dialog button; select the range on the worksheet. When you are finished, click the Redisplay Dialog button.

6. Repeat Step 5 for any arguments you are using, and choose OK when finished.

   Note

   You can also type an equal sign and the function name (for example; =SUM) and press Ctrl+A to display the Formula Palette.

Use Basic Functions

Functions are built-in formulas that perform special calculations automatically. It may be used by itself, or it may be combined with other functions.

Excel provides multiple functions; some are used for statistical and financial analysis, while others are used in database operations.

A function may be entered using the keyboard, the mouse or the combination of both.

The Sum Function

The Sum function adds a value in a range. If you were adding the cells B5, C5, D5, E5 and F5, the function would read as =SUM(B5:F5).

AutoSum - Because the SUM function is used frequently, Excel provides the AutoSum button on the Standard toolbar.

To use the Sum function, follow these steps:

1. Select the cell where you want to enter the function.

2. Type =SUM(.

3. Enter the range of cells to sum up and then type the close parenthesis ()).

4. Press Enter.

Note

You can enter the range by dragging the mouse. If the cells are non-adjacent, hold down the Ctrl key and then click the cells.

Exercise 1

1. Open a new workbook.

2. Type the following data as shown in the illustration.

   

3. Add formatting to your worksheet.

4. Using the Sum function, compute for Total Quiz of each student.

5. Save your work as Sum Function.xls at Excel-Activities folder.

Average Function

The Average function gets the average of the values in range. For example, to get the average of the values in A1, B1, and C1, the formula would appear as:

To use the Average function using the keyboard, follow these steps:

1. Select the cell where you want the result to appear.

2. Type =AVERAGE(.

3. Enter the range of cells, and then end it with the close parenthesis.

4. Press Enter.

To use the Average function using the Insert Function, do the following:

1. Select the cell where you want the result to appear.

2. Do any of the following:

   • Click the Insert Function button on the Formula bar.

   • Click the drop-down arrow beside the AutoSum button and choose Average.

   • Type = equal sign and then choose Average from the drop-down list beside the Formula bar.

     The Insert Function dialog box appears.

     

3. From the list of Most Recently Used Function Category, choose Average.

4. Click OK. The Function Arguments dialog box appears.

   

5. Enter the range of cells.

6. Click OK.

Exercise 2

1. Open the file SUM Function.xls

2. Add another column beside Total Quiz. Name the column Average.

   

3. Compute for the average of the quizzes of each student.

4. Make sure the Heading is in the middle of the table.

5. Save your work.

Statistical Functions

MIN (Minimum)	- Indicates the lowest value in a range.
MAX (Maximum)	- Indicates the highest value in a range.
MEDIAN	- Returns the median of the given numbers. The median is the number between the middle of a set of numbers.

Example: Median (1,2,3,4,5) equals 3

The methods to use these functions are similar in using Sum or Average functions. But the easiest way is to type in the function then select the range of cells.

Exercise 3

1. Open the file Sum Function.xls.

2. Type MIN in cell B18, MAX in cell B19 and MEDIAN in cell B20.

3. Compute for MIN, MAX and MEDIAN.

   

4. Save your work.

Whiz Words

Sum	Average
MIN	MAX
MEDIAN	Function

Lesson Summary

Excel provides multiple functions. Functions are built-in formulas that perform special calculations automatically. Some of the basic functions in Excel are Sum, Average, Min, Max and Median.

Study Help

Directions: Answer the following correctly.

1. What is a function?

   ---

   
   

   ---

2. What do you think is the easiest way in using the Sum function? Explain your answer.

   ---

   
   

   ---

   
   

   ---

3. What is a median?

   ---

   
   

   ---

   
   

   ---

4. What is the use of the Average function?

   ---

   
   

   ---

   
   

   ---

5. What is the difference between MIN and MAX?

   ---

   
   

   ---

   
   

   ---

Activity 1

1. Create a Grading Sheet. Use the illustration below as your guide.

2. Encode at least 10 students with their corresponding grade in each subject. Before you compute the final grade, you should get first their final grade for PEHM, by getting their grade in PE, Health, and Music.

   

3. Get the MIN, MAX, and MEDIAN of each subject including the final grade.

4. Save your work as Summary of Functions.xls in the Excel-Activities folder.