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:
- ( ) Parenthesis
- : Reference Operator
- % Percent
- ^ Exponent
- * and / Multiplication and Division (from left to right)
- + 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.
To open the data file in Step 2, click on the link. If you are not able to download the file, ask your teacher for help.
- Start Microsoft Excel.
- Open the data file Allowance.
- Save the file as Your Name Allowance.
- Select cell I4 in the Remaining Money column.
- Press the = key to start a new function.
- Select cell D4, and then press the – key.
- 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
- 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).
- The Function Arguments box opens.
- In the Number 1 field, key the range E4:G4 (see Figure 2).
Figure 2 The Function Arguments dialog box
- Click the OK button. You have now added the cells that display Patricia's monthly expenses. These are then subtracted from her monthly allowance.
- 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.
- Repeat steps 2 through 9, using the appropriate cells to find the Remaining Money amounts for the rest of the students.
- Save your file.
Work with More Worksheet Formulas and Functions – Part 2Add 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.
- In your Allowance file, select cell B15 (you may need to scroll down).
- Click the Insert Function button to start a new formula. The Insert Function dialog box opens.
- Select MAX and click the OK button (Note: You may need to scroll through the Or select a category list and select All.)
- In the Function Arguments box, type the range C4:C9 in the Number 1 field.
- Click the OK button. The highest weekly allowance appears in cell B15.
- 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
- Key MIN to start the second function in the formula.
- Key (C4:C9). The displayed formula shows the minimum allowance in Column C subtracted from the maximum allowance.
- 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.
Work with More Worksheet Formulas and Functions – Part 3
- Save your file.
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.
- In your Allowance file, select cell B16.
- 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).
- 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.
- Key the letter A.
- Select the second letter B.
- Key the letter A again.
- Press the Enter key.
- The total number of students appears in cell B16 (see Figure 7).
Figure 7 The COUNT function adds up the students.
Work with More Worksheet Formulas and Functions – Part 4
- Save your file.
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.
- In your Allowance file, select cell E10.
- Key =SUM(E4:E10) in the Formula bar.
- Press the Enter key.
- Read the circular reference notification that appears (see Figure 8).
Figure 8 The circular reference notification
- Click the Cancel button on the error message to continue (click OK for more information on circular references).
- Select cell E10 again.
- Select the number 10 in the formula bar and key the number 9.
- 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.
- Save and close your Allowance file.
- Exit Excel