Page 153 - Excel Workbook for Dummies

15_798452 ch10.qxp 3/13/06 7:33 PM Page 136
136 Part II: Using Formulas and Functions
Note that if the values in the number arguments of the AVERAGE function contain
cells with text entries, logical values (TRUE or FALSE), or that are blank, Excel ignores
them in the counting calculation (they are naturally ignored in the summing). However,
if the cells in the number arguments contain 0 (zero) values, they are used in the
counting calculation (even though they add nothing to the sum).

In addition to the AVERAGE function used to calculate the arithmetic mean in range or
series of values, Excel also includes a MEDIAN function, which takes the same kind of
arguments. Instead of the arithmetic mean, the MEDIAN function returns the value
that lies precisely in the middle of those in the range or series specified as its argu-
ments, with half greater and half less.

Try It

Exercise 10-1: Building Formulas with the AVERAGE and MEDIAN Functions

If Excel is not currently running, launch the program. Then, open the Exercise10-1.xls
workbook file in your Chapter 10 folder inside the My Practice Spreadsheets folder or
in the Excel Workbook folder on the workbook CD-ROM. This workbook contains a
Home Sales-06 worksheet with a concise data table showing the recent house sales
during April and May in a small subdivision. You can use the sampling in this sales
table to practice using the AVERAGE and MEDIAN functions:

1. Select the cell range D4:D8 in the Home Sales-06 worksheet and then assign to
this selected cell range, the range name, Selling_price.
2. Position the cell cursor in cell D10 and then enter a formula there using the
AVERAGE function (in the Statistical function category in the Insert Function
dialog box) with the range Selling_price as its argument.
Use the Insert➪Name➪Paste command to insert the range name in the Number1
text box in the Function Arguments dialog box for the AVERAGE function.
3. Enter the following formula in cell E10:
=SUM(Selling_price)/COUNT(Selling_price)
As you can see, this division formula using the SUM and COUNT functions in cell
E10 returns the same value as the AVERAGE function in D10.
4. Enter a formula in cell D12 using the MEDIAN function that uses the Selling_price
range name as its sole argument.
Note the difference between the average and the median sales price as computed
by Excel in cells D10 and D12. The average selling price for a home in the sample
shown in this table is nearly $100,000 more than the median.
5. Save your work in a new workbook file named Solved10-1.xls in the Chapter 10
folder inside the My Practice Spreadsheets folder and leave this file open in
Excel for the next exercise.

Finding the Highest and Lowest Values

The MAX and MIN functions compute the highest and lowest values in a cell range
or series, respectively. They take the same type of arguments as the AVERAGE and
MEDIAN statistical functions. Although they may not seem very powerful when using
them on very small samples (such as the selling prices in the Home Sales-06 work-
sheet you used in Exercise 10-1), where you can visually pick out the highest and