The `PERCENTRANK.EXC` function in Excel is used to determine the rank of a value in a data set as a percentage. This function excludes the 0 and 1 endpoints of the range, unlike `PERCENTRANK.INC` which includes them. Here’s how you can use the `PERCENTRANK.EXC` function:
Syntax
PERCENTRANK.EXC(array, x, [significance])
- `array`: This is the range of data for which you want to determine the percentage rank.
- `x`: The value for which you want the rank. This value should be within the `array`.
- `[significance]`: This is an optional argument that specifies the number of significant digits for the returned percentage. If omitted, Excel defaults to three significant digits (0.xxx).
Steps to Use
- Set Up Your Data: Ensure that you have a range of data values in your Excel sheet.
- Select a Cell: Click on the cell where you want the result to be displayed.
- Enter the Formula:
- Start by typing `=PERCENTRANK.EXC(`.
- Select your data range for the `array` argument. For example, if your data is in cells A1 through A10, you’d enter `A1:A10`.
- Specify the value `x` for which you want to find the percentage rank.
- Optionally, specify the `significance`.
- Close the Parentheses and Press Enter: The final formula may look something like `=PERCENTRANK.EXC(A1:A10, A5, 4)`. This calculates the rank of the value in cell A5 within the range A1:A10, showing 4 significant digits.
Example
Suppose you have a set of numbers in cells B1 to B10 and you want to find the percentage rank of the value in B5. You could use the formula:
=PERCENTRANK.EXC(B1:B10, B5)
If the value in B5 is not precisely in the array, Excel will interpolate the rank between the two closest values.
Things to Keep in Mind
- Ensure that the value `x` you are ranking is within the specified `array`.
- If `x` is equivalent to one of the endpoints when using `PERCENTRANK.EXC`, it might return an error, since this function doesn’t include the exact 0 and 1 endpoints (use `PERCENTRANK.INC` if needed).
- If required precision is important, make sure to specify `significance` according to your needs.
By following these steps, you should be able to easily use `PERCENTRANK.EXC` in Excel to find the percentage rank of a value within a dataset.
Unlock Your Potential
Excel
Access
Power BI
