Percentiles - getting mixed results when trying to find the 10th and 90th percentiles

M

mathy555

New member
Joined
May 11, 2016
Messages
5
Hello everyone,

I was working with various datasets trying to find the 10th and 90th percentiles. I am doing my work within Microsoft Excel. When I was working with one of my datasets, I had a #NUM return, so this prompted me to verify something by hand. When I did this, mass confusion occurred as I was getting different results.

I am hoping to get insight as to which method is the best and what is attributing for the differences.

The dataset contains the following values:

41.3
44.1
45.0
54.2
54.6
55.1
58.4
62.1
80.2
112.5

Based on input from someone, I was using the PERCENTILE.EXC function.

So, using the PERCENTILE.EXC function

10th percentile: 41.6
90th percentile: 109.3

However, the PERCENTILE.INC function yields:

10th percentile: 43.8
90th percentile: 83.4

I am fairly confident it’s .EXC I want to be using.

When I do this by hand using the following equation

R = (P/100)(n + 1) where R is the position in the dataset where the percentile (P) is located.

I get

10th percentile: 42.7
90th percentile: 96.4

I am very perplexed here, why is the equation by hand yielding significantly different values (especially 90th percentile).

I want to make sure my work here with these percentiles is as accurate as possible. This is for a project I am doing for my own leisure.
 
mathy555 said:
Hello everyone,

I was working with various datasets trying to find the 10th and 90th percentiles. I am doing my work within Microsoft Excel. When I was working with one of my datasets, I had a #NUM return, so this prompted me to verify something by hand. When I did this, mass confusion occurred as I was getting different results.

I am hoping to get insight as to which method is the best and what is attributing for the differences.

The dataset contains the following values:

41.3
44.1
45.0
54.2
54.6
55.1
58.4
62.1
80.2
112.5

Based on input from someone, I was using the PERCENTILE.EXC function.

So, using the PERCENTILE.EXC function

10th percentile: 41.6
90th percentile: 109.3

However, the PERCENTILE.INC function yields:

10th percentile: 43.8
90th percentile: 83.4

I am fairly confident it’s .EXC I want to be using.

When I do this by hand using the following equation

R = (P/100)(n + 1) where R is the position in the dataset where the percentile (P) is located.

I get

10th percentile: 42.7
90th percentile: 96.4

I am very perplexed here, why is the equation by hand yielding significantly different values (especially 90th percentile).

I want to make sure my work here with these percentiles is as accurate as possible. This is for a project I am doing for my own leisure.
Click to expand...
  1. PERCENTILE.EXC: This function excludes the percentile value itself from the calculation. Use when you want to find a value below which a certain percentage of data falls.
  2. PERCENTILE.INC: This function includes the percentile value itself in the calculation. Use when you want to find the value at a certain percentile rank within a dataset.
The main difference is how they handle the rank.
  1. PERCENTILE.EXC excludes the percentile value from the calculation if the rank is not a whole number. It linearly interpolates between the values at the floor rank and the ceiling rank.
  2. PERCENTILE.INC includes the percentile value in the calculation. It does not perform interpolation and returns the value at the floor rank.
 
mathy555 said:
I am very perplexed here, why is the equation by hand yielding significantly different values (especially 90th percentile).

I want to make sure my work here with these percentiles is as accurate as possible. This is for a project I am doing for my own leisure.
Click to expand...

The problem is, there are different ways to define percentile! That makes "accurate" something of a misnomer.

What factors influence your choice of a definition (such as having been told to use one, or just habit)? You have to decide.
 
BigBeachBanana said:
  1. PERCENTILE.EXC: This function excludes the percentile value itself from the calculation. Use when you want to find a value below which a certain percentage of data falls.
  2. PERCENTILE.INC: This function includes the percentile value itself in the calculation. Use when you want to find the value at a certain percentile rank within a dataset.
The main difference is how they handle the rank.
  1. PERCENTILE.EXC excludes the percentile value from the calculation if the rank is not a whole number. It linearly interpolates between the values at the floor rank and the ceiling rank.
  2. PERCENTILE.INC includes the percentile value in the calculation. It does not perform interpolation and returns the value at the floor rank.
Click to expand...
Thank you so much for this detailed response. This helps me out immensely. Your explanations of EXC and INC are much better than what I’ve come across. This provides a great deal of clarity.
 
You must log in or register to reply here.
Top