The average of the highest 10%, 50% and 80% of given values ?

[lc1602]

Hello All,

mea culpa if the questions seems to be banal for most of you.

I have just started exploring Excel formulas.

= Average( ..... then which formula)


Thank you.

 

[Hui]

If you have a few number

=AVERAGE(1,2,3,4)

or a Range of Numbers

=Average(A1:A10)


or a combination

=AVERAGE(1,2,3,4,A1:A10)


Change range to suit

 

[lc1602]

That's great but how to get to the eg. highest 10%/50%/80% of let's say range A1:A10 ?

 

[keymaster]

Ok, that is a bit tricky, but doable. Here is how you can go about it:

=SUMPRODUCT((A1:A10>PERCENTILE(A1:A10,0.9))*(A1:A10)) / SUMPRODUCT(--(A1:A10>PERCENTILE(A1:A10,0.9)))

Replace 0.9 with 0.5 or 0.2 as needed for other 2 values.


Also see this for some more ideas: http://chandoo.org/wp/2010/06/04/average-of-top-5-values/

 

[Hui]

It is even trickier than that, as it depends on the distribution of the data


If it is a flat distribution ie: every number has the same chance of occurring you can use

the 80% is =MIN(A1:A10)+0.8*(MAX(A1:A10)-MIN(A1:A10))

and so the average of points above this will be

[code]=SUMPRODUCT((A1:A10>(MIN(A1:A10)+0.8*(MAX(A1:A10)-MIN(A1:A10))))*(A1:A10)) / SUMPRODUCT(--(A1:A10>(MIN(A1:A10)+0.8*(MAX(A1:A10)-MIN(A1:A10)))))


If the data is normally distributed use:

=NORMINV(0.8,AVERAGE(A1:A10),STDEV(A1:A10))

and the average above this will be

=SUMPRODUCT((A2:A11>(NORMINV(0.8,AVERAGE(A1:A10),STDEV(A1:A10))))*(A2:A11)) / SUMPRODUCT(--(A2:A11>(NORMINV(0.8,AVERAGE(A1:A10),STDEV(A1:A10)))))[/code]


Change the 0.8 in both cases to suit

 

[keymaster]

@Hui... Why do we need to worry about distributions of values? Cant we use percentile(), can you point what would go wrong with my approach?

 

[Hui]

Nothing is wrong with percentile if the data is evenly distributed,

ie: every outcome/measure has the same chance of occuring, like rolling a dice.


If the data isn't evenly distributed but has a differnet distribution you need to allow for that.

 

[keymaster]

Agree... Thanks for the clarification

 

[lc1602]

Thanks a lot for your help