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?

Handling Blank Cells in Quartile and Percentile Functions in Excel 2003

Yes, there is a way to handle blank cells when using the quartile and percentile functions in Excel 2003. You can use the IF and ISNUMBER functions to check if a cell is blank or not before including it in the calculation.

Here's an example of how to use the quartile function with blank cells:

1. Select the cell where you want to display the quartile result.
2. Type the following formula:
   Formula:

   =QUARTILE(IF(ISNUMBER(A1:A10),A1:A10),1) 

3. Press Ctrl+Shift+Enter to enter the formula as an array formula.

In this example, A1:A10 is the range of cells you want to calculate the quartile for. The IF function checks if each cell in the range is a number or not. If it is a number, the cell is included in the calculation. If it is blank, the cell is excluded from the calculation. The ISNUMBER function returns TRUE if the cell is a number and FALSE if it is not. The QUARTILE function then calculates the quartile for the remaining cells.

You can use a similar formula for the percentile function:

In this example, the percentile function calculates the 50th percentile for the range A1:A10, excluding any blank cells.

Using these formulas will ensure that your quartile and percentile calculations are accurate, even if some cells in the range are blank.

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!
--
Gary's Student


"pdmunger" wrote:

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?

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"pdmunger" wrote in message
...
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?