This tip was printed from the Tips for Spreadsheets website at http://tipsforspreadsheets.com/.

Microsoft Excel 2003 Functions and Formulas - Display Top or Bottom Quarter of a Set

When calculating sales figures or other sets of numbers, it may prove useful to not only display the top, bottom, or median of the group, but to also display the 25th or 75th percentile of the series. This way you can easily single out your top salespeople - those reaching the top 25th percentile or higher, and determine those who are in the bottom quartile and may need extra assistance...

=quartile(SERIES,QUARTER)

SERIES is the range of cells you want to evaluate.

QUARTER determines the quarter of values you want returned:

0: Lowest number
1: Lowest 25%
2: Median value
3: Top 25%
4: Top number

Assume the following spreadsheet:


A B
1 $2,587.23
2 $11,923.62
3 $89,132.48
4 $56,479.23
5 $14,829.17
6 $498.23
7 $12,893.87
8 $2,367.97
9 $5,493.55
10 $13,795.12
11 $6,339.01
12 $234.55
13
14 Top Sales Value =QUARTILE(A1:A12,4)
15 Top 25% =QUARTILE(A1:A12,3)
16 Median Sales Value =QUARTILE(A1:A12,2)
17 Bottom 25% =QUARTILE(A1:A12,1)
18 Bottom Sales Value =QUARTILE(A1:A12,0)

Cells B14 - B18 would have the following values:

B14: $89,132.48
B15: $14,053.63
B16: $9,131.32
B17: $2,532.42
B18: $234.55

Note that the author takes no responsibility in using this information in tracking performance data as statisticians know an extremely large or extremely small number can skew a series...



Press the "print" button on your browser or select "File" - "Print" to print this tip. Then, return to Microsoft Excel 2003 Functions and Formulas - Display Top or Bottom Quarter of a Set.
 
Standard disclaimer applies - Read http://tipsforspreadsheets.com/copyright-disclaim.html.