# Calculating the 95th percentile: Comparing normal distribution, R Quantile, and Excel approaches

I was trying to compute the 95th percentile on the following dataset. I came across a few online references of doing it.

### Approach 1: Based on sample data

The first one tells me to obtain the TOP 95 Percent of the dataset and then choose the MIN or AVG of the resultant set. Doing so for the following dataset gives me:

AVG: 29162
MIN: 0


### Approach 2: Assume Normal Distribution

The second one says that the 95th percentile is approximately two standard deviations above the mean (which I understand) and I performed:

AVG(Column) + STDEV(Column)*1.65: 67128.542697973


### Approach 3: R Quantile

I used R to obtain the 95th percentile:

> quantile(data$V1, 0.95) 79515.2  ### Approach 4: Excel's Approach Finally, I came across this one, that explains how Excel does it. The summary of the method is as follows: Given a set of N ordered values {v[1], v[2], ...} and a requirement to calculate the pth percentile, do the following: • Calculate l = p(N-1) + 1 • Split l into integer and decimal components i.e. l = k + d • Compute the required value as V = v[k] + d(v[k+1] - v[k]) This method gives me 79515.2 None of the values match though I trust R's value is the correct one (I observed it from the ecdf plot as well). My goal is to compute the 95th percentile manually (using only AVG and STDEV functions) from a given dataset and am not really sure what is going here. Can someone please tell me where I am going wrong? 93150 93116 93096 93085 92923 92823 92745 92150 91785 91775 91775 91735 91727 91633 91616 91604 91587 91579 91488 91427 91398 91339 91338 91290 91268 91084 91072 90909 86164 85372 83835 83428 81372 81281 81238 81195 81131 81030 81011 80730 80721 80682 80666 80585 80565 80534 80497 80464 80374 80226 80223 80178 80178 80147 80137 80111 80048 80027 79948 79902 79818 79785 79752 79675 79651 79620 79586 79535 79491 79388 79277 79269 79254 79194 79191 79180 79170 79162 79154 79142 79129 79090 79062 79039 79011 78981 78979 78936 78923 78913 78829 78809 78742 78735 78725 78618 78606 78577 78527 78509 78491 78448 78289 78284 78277 78238 78171 78156 77998 77998 77978 77956 77925 77848 77846 77759 77729 77695 77677 77382 70473 70449 69886 69767 69704 69573 69479 69398 69328 69311 69265 69178 69162 69104 69100 69072 69062 68971 68944 68929 68924 68904 68879 68877 68799 68755 68726 68666 68623 68588 68547 68458 68457 68453 68438 68438 68429 68426 68394 68374 68363 68357 68337 68300 68256 68250 68228 68216 68180 68149 68124 68114 68060 68029 68029 68025 68004 67996 67981 67964 67938 67925 67914 67901 67853 67819 67818 67788 67770 67767 67688 67670 67669 67629 67618 67609 67602 67583 67540 67479 67475 67470 67433 67420 67387 67343 67339 67337 67315 67273 67224 67208 67160 67137 67102 67045 66449 66408 66338 66211 63784 63557 63091 63021 62895 62663 62182 62079 62044 61907 61888 61856 61847 61792 61764 61683 61641 61612 61514 61511 61503 61411 61263 61248 60965 60941 60907 60876 60773 60669 60537 60525 60387 60194 59673 59576 59561 59556 57652 57458 57308 57264 57158 57106 56288 56245 56054 56031 55930 55841 55533 55532 55316 55281 55230 55196 55111 55101 50957 50870 49580 48353 21349 21319 21288 21274 21270 21255 21232 21208 21196 21184 21164 21150 21149 21143 21129 21108 21100 21072 21043 20934 20912 20908 20882 20871 20858 20843 20839 20834 20800 20790 20788 20757 20752 20748 20744 20739 20721 20712 20710 20671 20620 20575 20572 20567 20551 20536 20522 20510 20484 20430 20415 20398 20368 20362 20357 20349 20347 20341 20338 20335 20335 20334 20332 20332 20332 20330 20326 20324 20323 20307 20304 20299 20297 20292 20282 20280 20275 20270 20270 20258 20257 20257 20256 20254 20252 20251 20247 20243 20231 20229 20223 20223 20221 20219 20217 20215 20212 20211 20210 20208 20202 20202 20202 20197 20192 20190 20190 20187 20186 20184 20179 20175 20175 20170 20170 20170 20166 20162 20158 20157 20157 20156 20153 20152 20151 20151 20148 20146 20141 20141 20139 20137 20133 20132 20130 20129 20124 20124 20123 20114 20109 20104 20104 20094 20092 20091 20088 20086 20085 20084 20083 20078 20076 20076 20070 20068 20065 20060 20052 20049 20045 20041 20040 20039 20037 20036 20036 20032 20032 20021 20020 20017 20009 20007 20007 20004 20004 20002 19989 19985 19974 19973 19973 19967 19961 19960 19959 19957 19953 19952 19950 19943 19942 19940 19940 19939 19937 19936 19935 19935 19925 19921 19920 19914 19908 19907 19900 19900 19900 19899 19899 19898 19898 19894 19893 19891 19891 19888 19888 19888 19883 19883 19882 19882 19880 19878 19875 19875 19874 19873 19871 19867 19864 19862 19861 19860 19857 19856 19854 19854 19848 19848 19844 19842 19840 19840 19835 19833 19831 19830 19828 19826 19820 19817 19812 19812 19811 19809 19805 19799 19792 19789 19788 19785 19780 19770 19765 19763 19762 19754 19743 19742 19738 19737 19735 19731 19724 19722 19721 19711 19710 19699 19698 19697 19695 19692 19687 19683 19672 19670 19665 19664 19660 19654 19651 19644 19643 19643 19641 19640 19620 19619 19618 19617 19614 19613 19608 19607 19607 19605 19579 19575 19568 19556 19553 19553 19551 19550 19548 19536 19535 19500 19500 19473 19462 19461 19455 19451 19391 19388 19386 19384 19375 19371 19353 19338 19318 19273 19271 19269 19265 19258 19230 19228 19222 19221 19221 19215 19196 19180 19177 19166 19161 19154 19148 19138 19134 19129 19116 19113 19107 19105 19102 19096 19092 19088 19085 19085 19083 19072 19067 19066 19061 19058 19050 19049 19045 19044 19043 19043 19032 19005 18996 18968 18957 18948 18938 18936 18920 18920 18913 18897 18897 18892 18884 18878 18878 18878 18871 18870 18869 18866 18864 18864 18864 18862 18862 18862 18860 18859 18858 18858 18853 18852 18852 18851 18851 18848 18847 18846 18846 18846 18845 18845 18844 18842 18841 18841 18840 18840 18837 18837 18836 18836 18835 18834 18833 18831 18830 18830 18829 18829 18829 18828 18826 18825 18823 18822 18822 18822 18821 18821 18821 18819 18819 18818 18816 18813 18812 18812 18812 18812 18810 18809 18809 18809 18809 18808 18808 18806 18805 18805 18804 18803 18802 18802 18801 18801 18801 18801 18800 18799 18799 18798 18797 18796 18796 18796 18795 18795 18793 18792 18792 18792 18791 18791 18791 18789 18789 18789 18788 18787 18783 18782 18782 18782 18781 18781 18780 18780 18779 18779 18779 18779 18778 18777 18777 18776 18775 18773 18773 18772 18772 18771 18770 18770 18770 18769 18769 18767 18767 18766 18762 18762 18761 18761 18761 18758 18757 18757 18756 18756 18755 18751 18750 18749 18749 18749 18746 18746 18746 18746 18746 18745 18745 18744 18744 18743 18742 18739 18739 18738 18737 18736 18734 18729 18729 18727 18727 18723 18723 18723 18723 18721 18721 18721 18719 18719 18719 18719 18718 18717 18716 18714 18710 18710 18710 18708 18707 18704 18702 18701 18701 18699 18695 18694 18692 18691 18690 18689 18689 18686 18684 18683 18681 18679 18675 18675 18672 18665 18665 18665 18658 18656 18655 18654 18654 18654 18652 18650 18649 18646 18645 18642 18640 18638 18638 18636 18633 18633 18631 18630 18629 18625 18625 18623 18622 18619 18617 18617 18616 18616 18614 18614 18614 18614 18611 18611 18609 18609 18600 18597 18596 18594 18593 18591 18589 18585 18580 18578 18578 18578 18572 18569 18567 18566 18565 18563 18559 18559 18557 18557 18554 18551 18548 18547 18545 18544 18544 18541 18539 18539 18536 18535 18531 18529 18526 18524 18524 18522 18517 18515 18503 18502 18497 18496 18496 18496 18495 18493 18492 18487 18487 18486 18486 18485 18482 18479 18473 18471 18470 18464 18463 18460 18459 18454 18454 18452 18450 18447 18446 18442 18442 18442 18440 18439 18434 18432 18427 18426 18425 18421 18416 18414 18408 18407 18407 18407 18403 18402 18398 18397 18396 18394 18393 18392 18391 18390 18383 18378 18357 18356 18354 18349 18342 18341 18338 18337 18336 18333 18328 18319 18314 18313 18302 18295 18295 18291 18291 18288 18284 18281 18278 18276 18272 18269 18268 18263 18262 18261 18259 18257 18251 18247 18240 18240 18238 18235 18235 18234 18232 18225 18222 18221 18214 18214 18213 18213 18210 18210 18206 18205 18204 18203 18194 18192 18191 18190 18187 18184 18179 18179 18179 18175 18171 18170 18156 18152 18151 18151 18149 18149 18148 18148 18147 18146 18140 18139 18137 18137 18136 18135 18135 18134 18133 18133 18128 18128 18127 18127 18125 18122 18121 18120 18120 18119 18117 18110 18108 18108 18099 18097 18096 18096 18095 18087 18085 18084 18083 18067 18060 18056 18056 18054 18053 18050 18049 18048 18038 18036 18033 18033 18028 18027 18025 18023 18022 18010 18010 18010 18000 17995 17983 17980 17978 17975 17974 17974 17968 17968 17967 17965 17964 17962 17961 17956 17955 17943 17938 17935 17934 17933 17932 17930 17925 17923 17919 17912 17912 17904 17897 17896 17894 17884 17880 17874 17872 17870 17865 17857 17856 17854 17854 17845 17843 17841 17836 17834 17831 17831 17828 17822 17821 17821 17816 17804 17803 17799 17798 17794 17794 17793 17790 17787 17786 17783 17782 17781 17777 17777 17777 17772 17772 17771 17766 17766 17758 17750 17747 17743 17715 17699 17694 17683 17682 17681 17668 17668 17630 17619 17617 17610 17609 17609 17607 17607 17599 17587 17565 17551 17542 17532 17531 17514 17514 17512 17509 17503 17483 17481 17475 17465 17463 17449 17433 17404 17397 17356 17356 17214 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0  • You first approach needs to be rewritten: it could be "take the top 5% of values and find the minimum of them", in this case 79586, or "take the bottom 95% and find the maximum of them", in this case 79535. Jul 23, 2011 at 12:19 ## 2 Answers The first approach is completely wrong and has nothing to do with the 95th percentile, in my opinion. The second approach seems to be based on an assumption that the data is normally distributed, but it should be about 1.645 standard deviations above the mean, not 2 standard deviations, and it looks like you realised this. This is a poor method if the data is not normally distributed. If you want to work out the 95th percentile yourself, order the numbers from smallest to largest and find a value such that 95% of the data is below that value. R probably uses some sort of interpolation between data points. A simple approximation might be sort(data$V1)[0.95*length(data$V1)]. Edited after comment from @Macro. • your solution would require data$V1 to be pre-sorted. More generally, sort(data$V1)[.95*length(data$V1)], would be the approximation you want. However, if .95*length(data$V1) is not an integer it would just round down to the nearest integer when indexing sort(data$V1), so this approximation would always underestimate in that case. Jul 23, 2011 at 5:25
• Thanks for your comment. I knew about the underestimation, which is why I called it a simple approximation, but I forgot to include the sorting. I'll edit the answer. Jul 23, 2011 at 6:28

Here are a few points to supplement @mark999's answer.

• Wikipedia has an article on percentiles where it is noted that no standard definition of a percentile exists. However, several formulas are discussed.
• Crawford, J.; Garthwaite, P. & Slick, D. On percentile norms in neuropsychology: Proposed reporting standards and methods for quantifying the uncertainty over the percentile ranks of test scores The Clinical Neuropsychologist, Psychology Press, 2009, 23, 1173-1195 (FREE PDF) discusses calculation of percentiles within a psychology norming context.

The following explores a few things in R:

### Get data and examine R quantile function

>  x <- c(93150, 93116, 93096, etc... [ABBREVIATED INPUT]
> help(quantile) # Note the 9 quantile algorithms
> rquantileest <- sapply(1:9, function(TYPE) quantile(x, .95, type=TYPE))
> rquantileest
95%      95%      95%      95%      95%      95%
79535.00 79535.00 79535.00 79524.00 79547.75 79570.70
95%      95%      95%
79526.20 79555.40 79553.49
> sapply(rquantileest, function(X) mean(x <= X))
95%       95%       95%       95%       95%
0.9501859 0.9501859 0.9501859 0.9494424 0.9501859
95%       95%       95%       95%
0.9501859 0.9494424 0.9501859 0.9501859

• help(quantile) shows that R has nine different quantile estimation algorithms.
• The other output shows the estimated value for the 9 algorithms and the proportion of the data that is less than or equal to the estimated value (i.e., all values are close to 95%).

### Compare with assuming normal distribution

> # Estimate of the 95th percentile if the data was normally distributed
> qnormest <- qnorm(.95, mean(x), sd(x))
> qnormest
[1] 67076.4
> mean(x <= qnormest)
[1] 0.8401487

• A very different value is estimated for the 95th percentile of a normal distribution based on the sample mean and standard deviation.
• The value estimated is around the 84th percentile of the sample data.

• The plot below shows that the data is clearly not normally distributed, and thus estimates based on assuming normality are going to be a long way off.

plot(density(x))

• has provided a very nice answer. I would only add that it seems to me that in most cases, the differences among the 9 estimates are so small as to matter very little. Jul 23, 2011 at 10:18
• The Wikipedia article on quantiles is better than the one on percentiles Jul 23, 2011 at 11:55
• Something is wrong here as R should be giving numbers between 75500 and 75600. Did some of the 1345 values get lost? Jul 23, 2011 at 12:23
• @Henry thanks for that. In my attempt to minimise the number of lines displayed for input on the question, I placed the c(...) command on only a couple of lines. As a result I think I encountered some form of command line length limit which was chopping off some of the data. I'd never seen this issue before because usually I'd have such data in a separate file. I've updated my script and the output so that the c(...) command now spans 120 lines; see gist gist.github.com/1102127 Jul 24, 2011 at 2:23
• +1 Thank you the additional information. Just when you posted this, out of curiosity I was looking at the distribution using a QQ-plot and reached the same conclusion. Thank you for your time. Jul 24, 2011 at 22:38