Page 97 - Excel Workbook for Dummies

10_798452 ch05.qxp 3/13/06 7:44 PM Page 80
80 Part II: Using Formulas and Functions

Q. Can I mix built-in functions with handmade construct manually, as in =SUM(A5:
formulas? J10)^2 to square the total of the range of
values in the cell range A5:J10 of the work-
A. Yes, most definitely: Excel functions can sheet returned by the SUM function.
serve as operand in any formula that you
Try It

Exercise 5-4: Building Formulas with AutoSum
Use your Practice Formulas.xls workbook to get some experience using the SUM,
AVERAGE, and COUNT functions in the spreadsheet. These functions are so fre-
quently used that Excel doesn’t even require you to access them from the Insert
Function dialog box, making them readily available from the AutoSum button on
Standard toolbar:

1. Drag through the cell range A4:A8 to select all their values in the Formulas work-
sheet of your Practice Formulas.xls workbook.
Note that the AutoSum indicator on the Status bar immediately shows you that
the total of the values in this range is 220 (Sum=220).
2. Right-click the AutoSum indicator and then select Average on its pop-up menu.
The AutoSum indicator on the Status bar changes to Average=44.
3. Return the AutoSum indicator to its default Sum setting and then click cell A9 to
place the cell cursor in this cell and deselect the range A4:A8.
4. Click the AutoSum button on the Standard toolbar (the one with sigma _ on it).
Excel immediately responds by entering the SUM formula, =SUM(A4:A8) in cell
A9. Note that the program also places a marquee bounding box around the cell
range A4:A8 in the worksheet as well as selects the argument A4:A8 in the SUM
function itself. This enables you to modify the range or ranges of cells to be
totaled by the SUM function either by editing its argument in the function or
dragging the boundaries of its marquee.
5. Click the Enter button on the Formula bar to complete this formula in cell A9.
Excel enters the formula, =SUM(A4:A8) in this cell as shown on the Formula bar
and displays the total of the range, 220, in the cell.
6. Click the drop-down button attached to the AutoSum button on the Standard
toolbar and then select Average on its drop-down list.
Excel now enters the formula =AVERAGE(A4:A8) for your editing or approval in
cell A9.

7. Click the Enter button on the Formula bar to enter this formula in the cell.
Excel enters the formula =AVERAGE(A4:A8) in this cell as shown on the Formula
bar, and displays the total of the range, 44, in the cell.
8. Use Undo to restore the SUM formula in cell A9 and then use the Count option on
the AutoSum drop-down list to create a formula in cell B9 that returns the
number of values in the range B4:B8.
The formula you construct in cell B9 should read =COUNT(B4:B9) and this for-
mula should return 5 as its result.
9. Replace the value 10% in cell B7 with the text entry, ten.
Ignoring the reappearance of the #VALUE! error in cell C7 for the moment, note
that the result returned to cell B9 changes from 5 to 4, indicating that the COUNT
function counts only the numeric entries in the cells you include in its argument.