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

winner!”,””).

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.

RANK

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?