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 function was introduced in Excel 2010 and has replaced the PERCENTRANK function.
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.
PERCENTRANK.EXC function Syntax
PERCENTRANK.EXC(array, x, [significance])
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.
PERCENTRANK.EXC function example
Formula in cell C7:
It interpolates in order to return the correct value if x doesn't match a value in the 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.
PERCENTRANK.EXC function not working
The PERCENTRANK.EXC function returns
- #NUM! error value if:
- array is empty
- [significance] < 1
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)
Functions in 'Statistical' category
The PERCENTRANK.EXC function function is one of many 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