Excel Percentile function versus Average using Pivot

[rialtmann]

Hi there,

I've done a bit of searching on the forum first so I dont duplicate but couldnt find an answer to the following:

I needed quartiles for a larger dataset of salaries and used the percentile function for that. In the process I also the 50th percentiel just for fun and found that it was very different to the pivot average (using the same criteria)

Here is the function I use:

=PERCENTILE(IF(('Master using EL'!$E$5:$E$4815=1)*('Master using EL'!$S$5:$S$4815="No")*('Master using EL'!$M$5:$M$4815='Calculations Quartile'!$A$3),'Master using EL'!$Z$5:$Z$4815,""),$B3/100)

Where B3 is either 25, 50, or 75.

Am I making a logical mistake in thinking that percentile 50 should be equal to the average?

Cheers
Ronja

 

[oldbrewer]

say you have 100 employees 99 earn 1000 dollars 1 earns 100,000 dollars....

average is clearly misleading so choose between median and mode - then see if you can answer your own question

 

[rialtmann]

Thanks for you answer. I am obviously confused by the actual definition of 50th percentile within excel. Would that be the median? Maybe thats where my thinking went wrong.

 

[oldbrewer]

yes 50th percentile and median are both defined as "half the results( in ascending order) are below here..................

 

[rialtmann]

Great. Thanks for helping me overcome my stupidity!