[Solved] PERCENTILE returns strange values

Discuss the spreadsheet application
Post Reply
neogrande
Posts: 3
Joined: Sat Jan 12, 2008 6:05 pm

[Solved] PERCENTILE returns strange values

Post by neogrande »

Hello all,

I have a list of values 1 to 20 in cells A1 to A20.
No I call the function "=QUANTIL(A1:A20; 0,02)" (which is the german version of "PERCENTILE" and get 1,38.

1. The description says, that this function takes alpha as percentage-value, but an input like "2" results in an error.

2. I think, this function should sort the list of values and give me the value at the alpha*100-percentage index.
If I do it manually, it look like the follows:
0,02 * 20 = 0,4
-> value at 0. position isn't available, so 1. position -> 1
Or for an 98-percentile:
0,98 * 20 = 19,6
-> value at 20. position: 20

I've tried some values and every time I get some strange values, which I do not have in my list.
How does the percentile-function works?
Last edited by neogrande on Sun Jan 13, 2008 12:55 am, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: PERCENTILE returns strange values

Post by Villeroy »

Problem #1: Type "0,02" or equivalent "2%" into a cell. The latter will apply some default %-format to an unformatted cell. Once the cell has a %-formatting you've got to type the values as percent.
Problem #2: Try a sequence from 0 to 20. You'll get the expected results (2%->0.4, 98%->19.6)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
neogrande
Posts: 3
Joined: Sat Jan 12, 2008 6:05 pm

Re: PERCENTILE returns strange values

Post by neogrande »

Yes, but the 1,38 still irritates me.

Think of following: If you have 100 values, then exact 1 value should fall into the 1%-percentile and exact 2 values should fall into the 2%-percentile.
If you have 20 values, only 0,4 values (so no value) will fall into the 2%-quantile.
If this quantile is set to 1,38 in the list of 1 to 20 there is one value in this quantile.

With this argumentation, even the 0,4 with the list from 0 to 20 is wrong. My interpretation of the 2%-quantile here is exact 0.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: PERCENTILE returns strange values

Post by Villeroy »

Referring to your initial example where A1:A20 = (1...20)
alpha = 2% which means 50 equal steps
(1...20) can be divided into 50 equal steps of 0.38 =(MAX($A$1:$A$20)-MIN($A$1:$A$20))*0.02
The 2% percentil of sequence (1...20) is 1+0.38
adding 50 steps to the minimum gives the maximum:
1+50*0.38=20
It does not matter which values are in between minimum 1 and maximum 20
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
neogrande
Posts: 3
Joined: Sat Jan 12, 2008 6:05 pm

Re: PERCENTILE returns strange values

Post by neogrande »

ok, I think, I got it.
Thanks
Post Reply