Microsoft Excel 2003 Functions and Formulas
Display Top or Bottom Quarter of a Set
Summary: Use the quartile function to display the max, min, median, or top or bottom 25% of a group of numbers.
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...
Add: 
Del.icio.us | 
Digg | 
Furl | 
My Yahoo!
Last Modified on: August 31, 2008, at 5:39 P.M. EDT
Return to the Microsoft Excel 2003 Functions and Formulas page.
Search the Site: