Microsoft Office Tutorials and References
In Depth Information
Analyzing Data with Percentiles and Bins
It so happens that the score that is positioned at the 90th percentile is 80.
Cell F4 has the formula =PERCENTILE.INC(B3:B27,0.9), which uses 0.9 as
the second argument.
The cells in C3:C27 all have a formula that tests whether the cell to the
left, in column B, is at or greater than the 90th percentile. For example, cell
C3 has this formula: =IF(B3>=PERCENTILE.INC(B$3:B$27,0.9),”A
If the value in cell B3 is equal to or greater than the value at the 90th
percentile, then cell C3 displays the text “A winner!”. The value in cell B3 is 59,
which doesn’t make for a winner. On the other hand, the value in cell B5 is
greater than 80, so cell C5 displays the message.
Here’s how to use the PERCENTILE.INC function:
1. Enter a list of numerical values.
2. Position the cursor in the cell where you want the result to appear.
3. Enter =PERCENTILE.INC( to start the function.
4. Drag the pointer over the list, or enter the address of the range.
5. Enter a comma ( ,).
6. Enter a value between 0 and 1 for the second argument.
This tells the function what percentile to seek.
7. Enter a ) and press the Enter key.
The RANK.EQ or RANK.AVG function tells you the rank of a particular
number, in other words where the value is positioned, within a distribution.
In a sample of ten values, for example, a number could be the smallest
(rank = 1), the largest (rank = 10), or somewhere in between. The function
takes three arguments:
✓ The number being tested for rank: If this number isn’t found in the
data, an error is returned.
✓ The range to look in: A reference to a range of cells goes here.
✓ A 0 or a 1, telling the function how to sort the distribution: A 0 (or if the
argument is omitted) tells the function to sort the values in descending
order. A 1 tells the function to sort in ascending order. The order of the
sort makes a difference in how the result is interpreted. Is the value in
question being compared to the top value of the data or the bottom value?