How to use the PERCENTRANK function
The PERCENTRANK function calculates the percent rank of a given number compared to the whole data set.
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 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 Function Syntax
PERCENTRANK(array, x, [significance])
PERCENTRANK 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 0 (zero). |
What are significant digits?
The PERCENTRANK 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 Function Example
The image above shows 15 numbers in column B. The formula calculates the percent rank for number 11.
Formula in cell C7:
2 numbers are less than 11 (second argument) and 12 numbers are larger than 11.
2/(2+12) = 0.142 (approx 3 digits)
What is percent?
A percent represents a fraction or ratio with a denominator of 100. The term comes from "per cent" meaning out of 100.
A percent expresses a number as a fraction of 100. Converting a fraction or decimal to a percentage involves multiplying by 100 and adding a % symbol. For example, 30% means 30/100 or 0.3. A percentage represents the relative proportion of some quantity compared to 100 units of that quantity
Percentages range between 0% to 100%. 0% means none while 100% means the total amount. They allow standardized measurement of ratios and comparison of part-to-whole relationships. Used frequently in statistics, business, banking, science and other areas involving relative amounts.
What if the x value is not in the array?
The PERCENTRANK function 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 Function not working
The PERCENTRANK function returns
- #NUM! error value if:
- array is empty
- [significance] < 1
Functions in 'Compatibility' category
The PERCENTRANK function function is one of 21 functions in the 'Compatibility' 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