Page 156 - Excel Workbook for Dummies

15_798452 ch10.qxp 3/13/06 7:33 PM Page 139
Chapter 10: Using Statistical Functions 139

4. Position the cell cursor in D16 and construct a formula using the COUNTBLANK
function with Sales_table as its argument that returns the number of empty cells
in this range.
5. Position the cell cursor in cell D10 and construct a formula that computes the
total number of cells in the Sales_table range.
The total number of cells in the Sales_table range is equal to the number of occu-
pied cells returned by the COUNTA function in cell D12 plus the number of
empty cells returned by the COUNTBLANK function is cell D16.
6. Position the cell cursor in cell D18 and create a formula with the COUNTIF func-
tion that returns the number of addresses in the cell range B4:B8 of the Sales
table that have the word Street in them.
When specifying text in the criteria argument of the COUNTIF function, you can
use the * (asterisk) as the wildcard character to stand in for multiple, unnamed
characters or the ? (question mark) to stand in for individual characters, as in
“*Street” to find the addresses of any length that end with the word Street.
7. Position the cell cursor in cell D20 and create a formula with the COUNTIF func-
tion that returns the number of selling prices in the cell range D4:D8 of the Sales
table that are above $600,000.
Don’t forget to enclose the criteria argument with the > (greater than) operator
in a set of double quotation marks.
8. Enter the value 400000 in cell F3 and then format it with Currency style with no
decimal places.
9. Edit the formula in cell D20 so that the criteria argument immediately following
the “>” (greater than) operator refers to contents in cell F3 rather than the static
value of 600000.
When referring to a cell reference in the criteria argument of the COUNTIF func-
tion, don’t omit the & (ampersand) text operator immediately following the “>”
(greater than) operator and immediately preceding the reference to cell F3.
10. Replace the $400,000 entered into cell F3 with $700,000.
Note that COUNTIF function immediately updates the result in cell D20 from 5 to
2 (only two entries in the range D4:D8, cells D4 and D8, have selling prices over
$700,000).
11. Save your workbook in a new file named Solved10-3.xls in your Chapter 10
folder inside the My Practice Spreadsheets folder on your hard disk and then
close the file.

Using the Statistical Functions in the

Analysis ToolPak Add-in

For you serious statisticians out there, the Analysis ToolPak add-in — which you first
encountered in Chapter 7 if you completed its exercises — contains a whole bunch
of extra statistical functions that may come in handy in your work. Before you can
access these supplementary statistical functions, you must install and activate
the Analysis ToolPak by selecting its check box in the Add-Ins dialog box (Tools➪
Add-Ins).