PERCENTRANK.INC function

The PERCENTRANK.INC function in Excel is used to calculate the relative percentile rank of a value within a data set, including the 0th and 100th percentiles. This function determines what percentage of the data falls below a specified value, based on the entire data range, including the extremes.

Syntax:

PERCENTRANK.INC(array, x, [significance])

Arguments:

• array: Required. The array or range of data values. This contains the data you want to analyze.
• x: Required. The value for which you want to calculate the relative percentile rank.
• significance: Optional. The number of significant digits to return in the result. If omitted, the default is 3 digits of precision.

Output:

The function returns the relative percentile rank of the value x within the given data array. The rank is expressed as a decimal between 0 and 1, representing the percentage of data points that fall below the value x.

How It Works:

• PERCENTRANK.INC calculates the relative rank of a value x within the data set by comparing it to all the values in the array, including the 0th and 100th percentiles (i.e., the extremes).
• The result tells you the proportion of the data that is below the given value x. For example, if the result is 0.75, it means that 75% of the data points are below the value.

Example 1: Finding the Percentile Rank of a Value

Suppose you have a data set in cells A1:A10:

5, 10, 15, 20, 25, 30, 35, 40, 45, 50

To find the percentile rank of the value 25 in this dataset, use the formula:

=PERCENTRANK.INC(A1:A10, 25)

This will return the percentile rank of 25 as a decimal value, which will be around 0.25, indicating that 25 is higher than 25% of the data points.

Example 2: Finding the Percentile Rank of the Maximum Value

Suppose you want to calculate the percentile rank of the maximum value in the dataset. In this case, the maximum value is 50.

Use the formula:

=PERCENTRANK.INC(A1:A10, 50)

This will return 1, indicating that 50 is higher than 100% of the data points in the dataset.

Example 3: Specifying the Significance Level

You can specify the number of decimal places you want the result to have. For instance, if you want the result to be rounded to 2 decimal places, use the formula:

=PERCENTRANK.INC(A1:A10, 25, 2)

This will return the percentile rank of 25 with 2 decimal places of precision.

Key Points:

• PERCENTRANK.INC includes the 0th and 100th percentiles, meaning the rank is calculated using the full data range, including the minimum and maximum values.
• The function returns a value between 0 and 1, where the result represents the proportion of the data that is less than the specified value.
• The significance argument is optional and controls the number of decimal places returned in the result.

Use Cases:

• Statistics: Find the percentile rank of a value in a dataset, including extreme values.
• Risk Management: Determine how a particular value compares to the rest of the data, including the highest and lowest values (e.g., in a financial analysis).
• Education: Calculate how a student’s score compares to the rest of the class, including the lowest and highest scores.
• Market Research: Compare the price or performance of a product to the full range of market data.

Notes:

• PERCENTRANK.INC includes the 0th and 100th percentiles, meaning the function calculates ranks using the entire range of values.
• If you want to exclude the extreme values (0th and 100th percentiles), use PERCENTRANK.EXC instead.