# Computer Concepts in Action ©2009

### Work with More Worksheet Formulas and Functions

Work with More Worksheet Formulas and Functions – Part 1

Insert a Function into a Formula

Formulas in Excel can contain several different kinds of values, including numbers, references to other cells, mathematical operators like + (addition) and * (multiplication), and functions like AVERAGE and COUNT. When several operations and functions are combined into a single formula, Excel performs the operation in the following order, or precedence:

1. ( ) Parenthesis
2. : Reference Operator
3. % Percent
4. ^ Exponent
5. * and / Multiplication and Division (from left to right)
6. + and - Addition and Subtraction (from left to right)

The order in which Excel calculates a formula can be changed by enclosing the part of the formula to be calculated first in parenthesis.

A Worksheet Function (SUM, AVERAGE, COUNT, etc.) is a built-in mathematical formula that makes it easier for you to accomplish tasks when working with large worksheets. Worksheet formulas often include one or more functions. In the next exercise, you will be using a data file to find out how much allowance money a student has left after her expenses. The worksheet already contains some functions, and you will be adding your own.

1. Start Microsoft Excel.

2. Open the data file Allowance. (7.0K)

3. Save the file as Your Name Allowance.

4. Select cell I4 in the Remaining Money column.

5. Press the = key to start a new function.

6. Select cell D4, and then press the key.

7. To add a function to the formula, click the Insert Function button. The Insert Function dialog box opens (see Figure 1).

Figure 1 The Insert Function dialog box <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078805775/595819/EnrichmentAct5_1_U05_SC01.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (48.0K)</a>

1. Select SUM and click the OK button (you may need to select All in the Or select a category drop-down list and scroll down to find the function).

2. The Function Arguments box opens.

3. In the Number 1 field, key the range E4:G4 (see Figure 2).

Figure 2 The Function Arguments dialog box <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078805775/595819/EnrichmentAct5_1_U05_SC02.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (108.0K)</a>

1. Click the OK button. You have now added the cells that display Patricia's monthly expenses. These are then subtracted from her monthly allowance.

2. The amount of money Patricia has remaining after her expenses appears in cell I4 (see Figure 3).

Figure 3 The formula has been applied to cell I4. <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078805775/595819/EnrichmentAct5_1_U05_SC03.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (277.0K)</a>

1. Repeat steps 2 through 9, using the appropriate cells to find the Remaining Money amounts for the rest of the students.

Work with More Worksheet Formulas and Functions – Part 2

Add a Second Function to a Formula

In the previous activity, you created formulas that used a single function to determine the amount of money that students had after monthly expenses. You can also use multiple functions in a formula to calculate additional information. In this exercise you will calculate the difference between the highest and lowest allowances in the worksheet.

1. In your Allowance file, select cell B15 (you may need to scroll down).

2. Click the Insert Function button to start a new formula. The Insert Function dialog box opens.

3. Select MAX and click the OK button (Note: You may need to scroll through the Or select a category list and select All.)

4. In the Function Arguments box, type the range C4:C9 in the Number 1 field.

5. Click the OK button. The highest weekly allowance appears in cell B15.

6. In the Formula bar, place your insertion point at the end of the displayed function. Then press the key (see Figure 4).

Figure 4 Add a second function <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078805775/595819/EnrichmentAct5_1_U05_SC04.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (274.0K)</a>

1. Key MIN to start the second function in the formula.

2. Key (C4:C9). The displayed formula shows the minimum allowance in Column C subtracted from the maximum allowance.

3. Press the Enter key. The difference between the highest and lowest weekly allowances appears in cell B15 (see Figure 5).

Figure 5 The formula is applied to cell B15. <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078805775/595819/EnrichmentAct5_1_U05_SC05.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (259.0K)</a>

Work with More Worksheet Formulas and Functions – Part 3

Fix an Error in a Formula

On occasion, a change in information or a minor mistake will make it necessary to edit or revise a formula or function. For example, you might accidentally try to apply a formula that references cells that contain only text. Perhaps a date, dollar amount, or cell reference range has been keyed in incorrectly.

To fix a formula, you can simply click on the cell in which the formula is contained, move the mouse pointer to the Formula bar, highlight the error or data to be changed, and replace it with the correct data.

1. In your Allowance file, select cell B16.

2. Look at the Formula bar. The cell range used for the COUNT function is for Student Name (B4 through B9), which in this instance is incorrect. The COUNT function adds up the number of cells which contain numbers (or a written number, like "one"). If the text cannot be translated into a number it is ignored. Therefore, the COUNT function should be applied to Student ID (A4 through A9).

3. In the Formula Bar, select the first letter B (see Figure 6).

Figure 6 Apply the COUNT function to the Student ID range of cells. <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078805775/595819/EnrichmentAct5_1_U05_SC06.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (257.0K)</a>

1. Key the letter A.

2. Select the second letter B.

3. Key the letter A again.

4. Press the Enter key.

5. The total number of students appears in cell B16 (see Figure 7).

Figure 7 The COUNT function adds up the students. <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078805775/595819/EnrichmentAct5_1_U05_SC07.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (250.0K)</a>

Work with More Worksheet Formulas and Functions – Part 4

Correct a Circular Reference

When using formulas and functions, errors like the one in the previous exercise are common, and that is why it is very important to enter data carefully and check your work. Circular references (entering a formula that references the same cell that contains the formula being created) are another common type of error.

1. In your Allowance file, select cell E10.

2. Key =SUM(E4:E10) in the Formula bar.

3. Press the Enter key.

Figure 8 The circular reference notification <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078805775/595819/EnrichmentAct5_1_U05_SC08.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (114.0K)</a>

1. Click the Cancel button on the error message to continue (click OK for more information on circular references).

2. Select cell E10 again.

3. Select the number 10 in the formula bar and key the number 9.

4. Press the Enter key. The total amount spent by all the students on fast food in a month appears in cell E10 (see Figure 9).

Figure 9 The circular reference has been corrected. <a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=jpg::::/sites/dl/free/0078805775/595819/EnrichmentAct5_1_U05_SC09.jpg','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (277.0K)</a>

1. Save and close your Allowance file.

2. Exit Excel