quartile, percentile, and blank cells

[pdmunger]

When I use the "quartile" and "percentile" functions in Excel 2003, I get a
wrong answer unless all the cells referenced are not empty.

Is there a "fix" for this, other than populating all the referenced cells?

 

[Gary''s Student]

You are correct.

QUARTILE() ignores blanks, but considers zeros real values. Here is a
little trick:

If your data in A1 thru A20 is:
11
22
29
30
30
35
39
39
42
45
49
53
55

64
67
72
78
88
92
then =QUARTILE(A$1:A$20,1) will return 32.5
In B1 enter:
=A1
and copy down. Notice that B14 is now a zero rather than a blank and
=QUARTILE(B$1:B$20,1) returns 30

Have a pleasant weekend!

 

[Jon Peltier]

Check your calculations and values. If the cell is truly blank, or if the
cell contains text, it is not included in the analysis, and the calculation
is correct. If a blank is replaced by zero, as in a too-simplistic formula,
the calculation is incorrect.

To check this I filled B7:E19 with these random values:

0.445020 0.445020 0.445020 0.445020
0.611949 0.611949 0.611949 0.611949
0.812069 0.812069 0.812069 0.812069
0.988183 0.988183 0.988183 0.988183
0.953357 0.953357 0.953357 0.953357
0.255361 0.255361 0.255361 0.255361
0.000000 0.238721
0.238721 0.238721 0.238721 0.983111
0.983111 0.983111 0.983111 0.057851
0.057851 0.057851 0.057851 0.513344
0.513344 0.513344 0.513344 0.324681
0.324681 0.324681 0.324681 0.605261
0.605261 0.605261 0.605261


Column B simply contains random numbers generated using RAND(). Column C
contains a direct link to column B, that is:

Cell C7: =B7

Column D contains a formula that inserts "" if column B contains a blank:

Cell D7: =IF(ISBLANK(B7),"",B7)

Column E contains the same formula as column C, but I deleted cell E13
(corresponding to the blank in B13), so the cells in column E below that
refer to one cell lower in column B:

Cell E13: =B14

So the values are all the same except for the zero in C13.

The averages, standard deviations, 25th percentile, and first quartile
values for each column of numbers are calculated as shown. The calculations
in Columns B through D include cells in row 7 through row 19 (including the
blank/zero/"" in row 13), while that in column E includes only rows 7
through 18 (not including the blank in row 19).

0.565742 0.522224 0.565742 0.565742 average
0.316793 0.341489 0.316793 0.316793 stdev
0.307351 0.255361 0.307351 0.307351 pctl 25
0.307351 0.255361 0.307351 0.307351 qrtl 1


The deviation is in column C, with the spurious zero value where the
original range contained a blank cell.

- Jon