Page 154 - Excel Workbook for Dummies

15_798452 ch10.qxp 3/13/06 7:33 PM Page 137
Chapter 10: Using Statistical Functions 137
lowest selling prices in an instant, they come in quite handy when dealing with
large data sets, where it would take a long time to locate these key values.

Try It

Exercise 10-2: Building Formulas with the MAX and MIN Functions
Use the Solved10-1.xls workbook you created at the end of Exercise 10-1 to practice
adding MAX and MIN functions to the spreadsheet table located on the Home Sales-06
worksheet:

1. Locate the cell cursor in cell D14 in the Home Sales-06 worksheet and construct a
formula using the MAX function with the range name Selling_price (assigned in
Exercise 10-1) as its argument to find the largest selling price in the range.
No surprise here: Excel returns $1,085,000 as the highest selling price in this
range.
2. Construct a formula in cell D16 using the MIN function with Selling_price as its
argument to find the lowest selling price in the range.
Again no surprise here: Excel returns $550,000 as the lowest selling price in the
range.
3. Insert two new rows into the sales table immediately above the row containing
the sales data for 566 Elm Street in the cell range B7:D7.
To insert two rows of blank cells in this table, select the cell range B7:D8 and
then choose Insert➪Cells and select OK when the Shift Cells Down option button
is selected.
4. Update the sales table by making the following data entries into the newly
inserted blank rows of cells:
• 211 River Road in cell B7, 5/15/06 in cell C7, and 495000 in cell D7

• 8989 King Place in cell B8, 5/23/06 in cell C8, and 1,500,000 in cell D8
Note the effect that your table edits have on the average price, high price, and
low price cells in the spreadsheet as calculated by the AVERAGE, MAX, and MIN
functions, respectively.
5. Save your work in a new workbook named Solved10-2.xls in the Chapter 10
folder inside the My Practice Spreadsheets folder and then close this work-
book file.

Counting Cells

Excel includes three counting functions, COUNTA, COUNT, and COUNTBLANK. You
can use these functions to build formulas that compute the number of cells in a
particular region or worksheet that are occupied, contain numeric entries, or are blank.
The syntax of these functions is as follows:

COUNTA(number1, [number2I], [. . .]) to return the number of nonblank cells in
the number argument(s)
COUNT(number1, [number2],[ . . . ]) to return the number of cells containing
numeric entries in the number argument(s)
COUNTBLANK(range) to return the number of blank cells in the range argument