How to use the PERCENTRANK.INC function

What is the PERCENTRANK.INC function?

The PERCENTRANK.INC function calculates the percent rank of a given number compared to the whole data set. PERCENTRANK.INC stands for "PERCENTRANK Inclusive". This includes the value of the score in the ranking. This function was introduced in Excel 2010 and has replaced the PERCENTRANK function.

1. Introduction

What is the percent rank?

The percent rank (or percentile rank) indicates the percentage of scores that fall at or below a given score in a dataset. It provides a way to evaluate the standing of a score relative to others.

What is relative standing?

Relative standing refers to how a particular score compares to the distribution of scores within a group or population. It allows you to understand where a score ranks in relation to others.

When is the PERCENTRANK.INC function useful?

Use the PERCENTRANK.INC function to evaluate the standing of an aptitude test score among all scores for the test.

What is an aptitude test score?

An aptitude test is a standardized test designed to measure a person's ability or skills in a specific topic. An aptitude test score is the performance measurement on such a test.

Evaluating an aptitude test score's percentage ranking allows understanding its standing compared to other test takers. Higher percentiles indicate better relative performance on the aptitude test.

2. PERCENTRANK.INC Function Syntax

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

3. PERCENTRANK.INC Function Arguments

array	Required. The array or range of data with numeric values that defines relative standing.
x	Required. The value for which you want to know the percent rank.
[significance]	Optional. Lower bound, default value is 3.

What are significant digits?

The PERCENTRANK.EXC function does not round the output number to the specified number of significant digits. If the specified number is 3 then 0.1875 becomes 0.187 which means that the remaining digits are simply removed.

4. PERCENTRANK.INC Function Example 1

In a class of 15 students, the scores on a final exam were recorded. What is their percentage rank among all the students if a student scored 75 using PERCENTRANK.INC? The data is:

Values
2
9
11
16
18
19
24
27
32
35
36
39
65
67
84

Here are the arguments:

• array = B23:B37
• x = 75

Formula in cell B17:

The formula calculates a value of 0.904 (or 90.4%) when a student scores 75, based on the data range B23:B37. The third argument, [significance], is optional and has a default value of three significant digits. The function performs interpolation to determine the appropriate value when the input value (x) does not directly correspond to a value in the data array.

The chart in the image visually represents this interpolation process, displaying the calculated value of 0.905 as an interpolated value falling between the data points 67 and 84.

To understand this graphical representation, locate the value 75 on the y-axis. From that point, draw an imaginary horizontal line until it intersects with the black line, which represents the interpolated line connecting the data points. Then, follow the point of intersection vertically downwards towards the x-axis. The corresponding value on the x-axis is approximately 90%.

The PERCENTRANK.INC function employs this interpolation technique to provide the correct value when the input value (x) does not match any value in the data array.

What is interpolate?

Interpolate means to estimate or infer a data point within a range of known data points. In statistics, interpolation refers to estimating the percentage between existing data points in a distribution.

5. PERCENTRANK.INC Function Example 2

A company has 15 sales representatives, and their monthly sales figures are recorded. If a representative sold $40,000 worth of products, what is their percentage rank among all the representatives using PERCENTRANK.INC?

Here are the arguments:

• array = B30:B44
• x = 40000
• significance = 3

Formula in cell B17:

The formula returns 0.407 (40.7%) if a sales representative sold for 40, 000 based on all sales representatives in B30:B44. The function interpolates in order to return the correct value if x doesn't match a value in the array. This is the case in this example, there is no data point equal to 40,000.

In the image above, locate value 40,000 on the y-axis. From that point, draw an imaginary horizontal line until it intersects with the blue line which represents the interpolated line between data points. Then, follow the point of intersection vertically towards the x-axis below. You will find that the corresponding value on the x-axis is approximately 41%.

6. PERCENTRANK.INC Function Example 3

In a marathon race with 25 participants, the finishing times of all runners are recorded. If an athlete finished the race in 3 hours and 20 minutes, what is their percentage rank among all participants using PERCENTRANK.INC?

Here are the arguments:

• array = B30:B54
• x = 3:20:00 (0.138888888888889)
• significance = 4

Formula in cell B17:

The formula returns 0.3737 (37.37%) if an athlete finished the race in 3 hours and 20 minutes, based on all data points in B30:B54. The function interpolates in order to return the correct value if x doesn't match a value in the array. This is the case in this example, there is no data point equal to 3:20:00.

In the image above, locate value 3:20:00 on the y-axis. From that point, draw an imaginary horizontal line until it intersects with the blue line which represents the interpolated line between data points. Then, follow the point of intersection vertically towards the x-axis below. You will find that the corresponding value on the x-axis is approximately 37%.

7. How is the PERCENTRANK.INC function calculated?

1. Sort the scores from lowest to highest.
2. Determine the rank of the given score in the ordered list.
3. Calculate the percent rank using the formula:
   Percent rank = (Rank - 1) / (Total number of scores - 1)

(3-1)/(15-1)

becomes

2/14 = 0.142 (approx. 3 digits)

8. PERCENTRANK.INC function not working

The PERCENTRANK.INC function returns

• #NUM! error value if:
  • array is empty
  • [significance] < 1

9. What is the difference between the PERCENTRANK.EXC and PERCENTRANK.INC functions?

The image illustrates the distinction between the calculations performed by the PERCENTRANK.EXC function (displayed in columns C19:C33) and the PERCENTRANK.INC function (displayed in columns D19:D33).

The formulas used for these calculations are:

PERCENTRANK.EXC = Rank / (Total number of values + 1)
PERCENTRANK.INC = (Rank - 1) / (Total number of values - 1)

To determine the rank, the following steps are followed:

1. Sort the values in ascending order from lowest to highest.
2. Assign a rank of 1 to the lowest value.
3. Assign a rank of 2 to the second-lowest value.
4. Continue this process, incrementing the rank for each subsequent value until all values have been ranked.

It's important to note that the PERCENTRANK.INC function always returns 0 for the lowest value and 1 for the highest value in the dataset.

Functions in 'Statistical' category

The PERCENTRANK.INC function function is one of 73 functions in the 'Statistical' category.

Excel function categories

Excel categories

Leave a Comment