Performing More Advanced Calculations

In this section, we ll perform some more calculations on the data in 2002 Jobs, starting with the average invoice. We ll also determine values for the highest and lowest invoices using various techniques.

Calculating the Average Value

To find the average of the invoices in this worksheet, you can use a formula that includes Excel s AVERAGE function. In this section, you ll use the Insert Function button to avoid errors and to make sure you include all the arguments Excel needs for the function. Follow these steps:

1. Click cell B5 , press Delete , and click the Insert Function button.

   Excel displays the Insert Function dialog box shown here:

   

   If necessary, click the Office Assistant s No button to decline its offer of help.

2. Select AVERAGE in the Select a function list, and click OK .

   Excel displays the Function Argument dialog box shown in this graphic:

   

   The dialog box contains a definition of the function and its arguments. In the Number1 text box, you can enter a number, cell reference, name , formula, or another function.

3. Click the button with the red arrow at the right end of the Number1 text box.

   The dialog box shrinks so that you can see more of the worksheet.

4. Select D13:D24 in the worksheet to add its reference to the formula.

5. Click the button with the red arrow again.

   The dialog box enlarges, and it now appears as shown in this graphic:

   

6. Click OK to enter the formula in cell B5.

   Excel displays $4,033.86 in cell B5.

Conditional formatting

To monitor a worksheet, you can use conditional formatting to highlight a cell that meets certain criteria. For example, you can display a cell s value in magenta if it is over 200,000. To apply this type of formatting, select the cell or cells , and click Conditional Formatting on the Format menu. In the Conditional Formatting dialog box, select a condition from the second drop-down list, and enter conditional parameters in the appropriate text boxes. Then click the Format button, select the formatting to be used to highlight the cell (for example, the color magenta), and click OK twice. When the value in the selected cell meets the condition or conditions you ve set, Excel highlights the cell with the specified formatting. If you want to delete conditional formatting, select the cell, click Conditional Formatting on the Format menu, click the Delete button, select the condition, and click OK twice.

 

Calculating with a Named Cell or Range

The last calculation you ll make with this set of data involves the Invoice Total value from cell B4. As a gross indicator of how well Tip Top Roofing fared in 2002, let s calculate the estimated profit. To make this calculation as flexible as possible, you can use names in the calculation so that the basic formula stays correct no matter how the size or location of the named cells and ranges changes.

Follow these steps to use named cells to calculate the total sales commission:

1. Select cell A7 , type Profit Margin , and press Tab .

2. Type 20% , and click the Enter button.

3. With cell B7 active, click Name and then Define on the Insert menu. Assign the name Profit_Margin to the cell, and then press Enter .

   You can also click the name box and type the name, as you did in an earlier procedure.

4. Select cell A8 , type Estimated Profit , and press Tab .

5. With cell B8 selected, type the formula =Total*Profit_Margin, and press Enter .

   Excel multiplies the value in the cell named Total (B4) by the value in the cell named Profit_Margin (B7), and then displays the result, $9,681.27, in cell B8. (Notice that the result of the formula is displayed in the Currency format because you preformatted the cell.)

6. Now select cell B7 , type 25% , and click the Enter button.

   Instantly, the value in cell B8 changes to reflect the new profit margin, as shown in this graphic:

   

   If a hundred calculations throughout the worksheet referenced the cell named Profit_Margin, Excel would adjust all their results to reflect this one change. Powerful stuff!

A function for every task

Excel provides many functions for common business and financial tasks ”some of them quite complex. To get more information about a function, display the Help window by clicking Microsoft Excel Help on the Help menu. On the Index tab, type function in the Type keywords text box, and click the Search button. In the Choose a topic list, click a specific function (for example, RATE). Excel then displays a description of the function, its syntax, and any other pertinent information in the pane on the right.