The `PERCENTRANK.INC` function in Excel is used to calculate the relative standing of a value within a data set as a percentage rank inclusive of both the smallest and largest values. It’s especially useful for determining the position of a value within a data set relative to its extremes.
Here’s how you can use the `PERCENTRANK.INC` function:
Syntax
PERCENTRANK.INC(array, x, [significance])
- `array`: The range or array of numbers.
- `x`: The specific value you want to find the percentage rank for within the array.
- `significance` (optional): The number of significant digits for the returned percentage rank. If omitted, it defaults to three significant digits (0.xxx).
Steps to use in Excel
- Prepare Your Data: Make sure you have your data organized in a column or row in Excel, as you will be using this range in your function.
- Select a Cell: Click on the cell where you want the result of the `PERCENTRANK.INC` function to appear.
- Enter the Function:
- Begin by typing `=PERCENTRANK.INC(`.
- Select or type the range of your data as the first argument (`array`).
- Add a comma and then enter the value (`x`) for which you want to find the percentage rank.
- Optionally, add another comma followed by the number of significant digits if you want to specify `significance`.
- Close the parenthesis and press `Enter`.
Example
Suppose you have a dataset in cells A1 to A10 and you want to find the percentage rank of the number 50. Here’s how you could use the function:
=PERCENTRANK.INC(A1:A10, 50)
If you wanted to specify four significant digits for the result, you would use:
=PERCENTRANK.INC(A1:A10, 50, 4)
Additional Notes
- Outcome Range: The result from `PERCENTRANK.INC` will be a decimal between 0 and 1, which can be converted to a percentage format in Excel by using the percentage format tool.
- Array Sorting: Though the function does not require the data to be sorted, a sorted data set can sometimes help visually verify the results.
- Boundary Values: `PERCENTRANK.INC` includes both the smallest and largest data points in its calculations, which distinguishes it from `PERCENTRANK.EXC`, which excludes those boundaries.
By following these steps, you can effectively use the `PERCENTRANK.INC` function to understand data distributions and ranking positions within Excel datasets.
Unlock Your Potential
Excel
Access
Power BI
