How to use the PERCENTRANK.EXC function
What is the PERCENTRANK.EXC function?
The PERCENTRANK.EXC function calculates the percentage rank of a given number in a data set. This is calculated by a given number's rank divided by the total number of values + 1
This function was introduced in Excel 2010 and has replaced the PERCENTRANK function.
Table of Contents
- Introduction
- PERCENTRANK.EXC Function Syntax
- PERCENTRANK.EXC Function arguments
- PERCENTRANK.EXC Function Example 1
- PERCENTRANK.EXC Function Example 2
- PERCENTRANK.EXC Function Example 3
- PERCENTRANK.EXC Function not working
- How is the PERCENTRANK.EXC Function calculated?
- What is the difference between the PERCENTRANK.EXC and PERCENTRANK.INC functions?
- Get Excel *.xlsx file
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.EXC function useful?
Use the PERCENTRANK.EXC 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.EXC Function Syntax
PERCENTRANK.EXC(array, x, [significance])
3. PERCENTRANK.EXC 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. Number of significant digits of the returned value, default value 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.EXC Function Example 1
In a class of 15 students, the scores on a final exam were recorded. If a student scored 75, what is their percentage rank among all the students? 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 returns 0.904 (90.4%) if a student scored 75 based on the data in B23:B37. The function interpolates in order to return the correct value if x doesn't match a value in the array.
The chart in the image above shows the calculated value 0.905 as an interpolated value between value 67 and 84.
In the image above, 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 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 90%.
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.EXC Function Example 2
A company has 20 sales representatives and their monthly sales figures are recorded. If a representative sold $50,000 worth of products, what is their percentage rank among all the representatives?
Here are the arguments:
- array = B30:B49
- x = 50000
- significance = 3
Formula in cell B17:
The formula returns 0.617 (61.7%) if a sales representative sold for 50, 000 based on all sales representatives in B30:B49. 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 50,000.
In the image above, locate value 50,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 62%.
6. PERCENTRANK.EXC Function Example 3
In a marathon race with 100 participant the finishing times of all runners are recorded. If an athlete finished the race in 3 hours and 30 minutes, what is their percentage rank among all participants?
Here are the arguments:
- array = B30:B129
- x = 3:30:00 (3.5/24)
- significance = 4
Formula in cell B17:
The formula returns 0.262 (26.2%) if an athlete finished the race in 3 hours and 30 minutes, based on all data points in B30:B129. 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:30:00.
In the image above, locate value 3:30: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 26%.
7. PERCENTRANK.EXC Function not working
The PERCENTRANK.EXC function returns
- #NUM! error value if:
- array is empty
- [significance]Â < 1
8. How is the PERCENTRANK.EXC Function calculated?
- Sort the scores from lowest to highest.
- Determine the rank of the given score in the ordered list.
- Calculate the percent rank using the formula:
Percent rank = Rank / (Total number of scores + 1)
If the array is {2;9;11;16;18;19;24;27;32;35;36;39;65;67;84} and 11 is the number to calculate the percent rank for then:
3/(15+1)
becomes
3/16 = 0.187 (approx. 3 digits)
9. What is the difference between the PERCENTRANK.EXC and PERCENTRANK.INC functions?
The image above shows how the calculations differ between the PERCENTRANK.EXC function (C19:C33) and the PERCENTRANK.INC function (D19:D33).
Here are the differences in how the results are calculated:
PERCENTRANK.EXC = Rank / (Total number of values + 1)
PERCENTRANK.INC = (Rank - 1) / (Total number of values - 1)
How to determine the rank:
- Sort the values from lowest to highest.
- The lowest value gets rank number 1.
- The second lowest gets rank number 2.
- Continue this until all values are ranked.
The PERCENTRANK.INC function always return 0 for the lowest value and 1 for the highest value.
Functions in 'Statistical' category
The PERCENTRANK.EXC function function is one of 73 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form