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