Microsoft Office Tutorials and References

In Depth Information

**Analyzing Data with Percentiles and Bins**

7. If you want to have the number evaluated against the list in

ascending
order, enter a comma (
,) and then enter a
1.

Descending order is the default and doesn’t require an argument to be

entered.

8. Enter a
)
and press the Enter key.

PERCENTRANK

The PERCENTRANK.INC or PERCENTRANK.EXC formula also returns the rank

of a value but tells you where the value is as a percentage. In other words,

the PERCENTRANK function may tell you that a value is positioned 20

percent into the ordered distribution. PERCENTRANK takes three arguments:

✓
The range of the sample

✓
The number being evaluated against the sample

✓
An indicator of how many decimal points to use in the returned answer

(This is an optional argument. If left out, three decimal points are used.)

PERCENTRANK.EXC is used when a rank between 0 and 100 percent (between

0 and 1) is to be returned, but not 0 or 1. In Figure 9-16, the percent rank of

the $4,800 value is calculated to be 82.3 percent (0.823). Therefore, $4,800

ranks at the 82.3 percent position in the sample. The formula in cell F8 is

=PERCENTRANK.INC(C3:C20,C9).

In the RANK.EQ function, the value being evaluated is the first argument, and

the range of the values is the second argument. In the PERCENTRANK.INC

function, the order of these arguments is reversed.

Follow these steps to use the PERCENTRANK.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
=PERCENTRANK.INC(
to start the function.

4. Drag the pointer over the list of values, or enter the address of the

range.

5. Enter a comma (
,).

6. Click the cell that has the value you want to find the rank for, or enter

its address.

You can also just enter the actual value.