Percentile in Statistics is method of evaluation individuals performance in a group or set of people. Percentile is not the ranking position in group of people however the method of analyzing the position in set of group of population. The possible method to evaluate the position is known as percentile calculation.
Let us take an example to understand better.
Let us take an example to understand better.
Assuming there are 25 students and their achieved scores.
Process steps to calculate.
1. Sort the scores in lowest to highest to calculate the Percentile.
2. Decide the Percentile kth number to calculate
I have calculated in above table to show 25th, 50th, 75th, 100th percentile as per the formula
(Percentile/100)*Total numbers
Now let us use the same concept in Excel Formula PERCENTILE or the other comfortable formula
In Excel version 2010 and above
PERCENTILE - Returns the kth value in set of values passed through array or range
Syntax: =PERCENTILE(array, k)
Where k is always between the range of 0 to 0.9
The above table is transposed horizontally, you can keep the values vertical to get the percentile result
=PERCENTILE.EXC(A2:Y2,0.9)
Result: 98.4
Transpose vertically and apply below formula.
=PERCENTILE.EXC(A2:A26,0.9)
Results: 98.4
It is always recommended that you to roundup the result to get accurate values
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 59 | 62 | 63 | 64 | 65 | 66 | 67 | 68 | 68 | 69 | 72 | 72 | 72 | 72 | 74 | 75 | 75 | 76 | 76 | 83 | 84 | 92 | 94 | 96 | 97 |
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | kth | Percentile | Population | kth (Rank) | Formula | Rounded Rank | Table Scores |
| 2 | 25th | 25 | 25 | 6.25 | =(B2/100)*C2 | 6 | 66 |
| 3 | 50th | 50 | 25 | 12.50 | =(B3/100)*C3 | 13 | 72 |
| 4 | 75th | 75 | 25 | 18.00 | =(B4/100)*C4 | 18 | 76 |
| 5 | 100th | 100 | 25 | 25.00 | =(B5/100)*C5 | 25 | 97 |
Process steps to calculate.
1. Sort the scores in lowest to highest to calculate the Percentile.
2. Decide the Percentile kth number to calculate
I have calculated in above table to show 25th, 50th, 75th, 100th percentile as per the formula
(Percentile/100)*Total numbers
Now let us use the same concept in Excel Formula PERCENTILE or the other comfortable formula
In Excel version 2010 and above
PERCENTILE - Returns the kth value in set of values passed through array or range
Syntax: =PERCENTILE(array, k)
Where k is always between the range of 0 to 0.9
The above table is transposed horizontally, you can keep the values vertical to get the percentile result
=PERCENTILE.EXC(A2:Y2,0.9)
Result: 98.4
Transpose vertically and apply below formula.
=PERCENTILE.EXC(A2:A26,0.9)
Results: 98.4
It is always recommended that you to roundup the result to get accurate values
=ROUNDUP(PERCENTILE.EXC(A2:Y2,0.9),0) Result: 96
Another method to evaluate the Percentile in group in Excel
=ROUNDUP(INDEX($A$2:$Y$2,0.9*25),0)
Result: 96
Index function indexes the range of data set internally, here we also passed the total count of numbers to tell that we are calculating for 25 people in row.
Benefit of this formula is the data set is indexed internally and you get the result.
This is very useful concept to evaluate individual's position in group. Now a days this process is followed by most of shortlisting process.
Related topic:
Calculate Weighted Average in Excel
Calculate Percentile in Excel
Another method to evaluate the Percentile in group in Excel
=ROUNDUP(INDEX($A$2:$Y$2,0.9*25),0)
Result: 96
Index function indexes the range of data set internally, here we also passed the total count of numbers to tell that we are calculating for 25 people in row.
Benefit of this formula is the data set is indexed internally and you get the result.
This is very useful concept to evaluate individual's position in group. Now a days this process is followed by most of shortlisting process.
Related topic:
Calculate Weighted Average in Excel
Calculate Percentile in Excel
No comments:
Post a Comment