In Calc, how can I average only the x highest values?
Ex. The following values: 60 | 75 | 80 | 85; the average of 3 highests is 80.
Currently I use: =AVERAGE(LARGE(B2:E2;1);LARGE(B2:E2;2);LARGE(B2:E2;3))
but if I have the 15 highests, is kind of tricky... Is there a simplest way?
Why you weren't warned that the values can be repeated? OK, try this solution
In cases where problems arise when dealing with ranges of cells, and it seems that the standard tools for processing is not enough, I recommend first looking in the direction of the function SUMPRODUCT(). This is the "Swiss knife" for Calc. For your example (IMHO!) will be sufficient this formula
=SUMPRODUCT(B2:E2;RANK(B2:E2;B2:E2;0)<4)/SUMPRODUCT(RANK(B2:E2;B2:E2;0)<4)
The value of this formula corresponds to your example for the three largest values. Replace "<4" in suitable conditions and you will get different values.
PS.Of course, the array formula
{=SUM((B2:E2)*(RANK(B2:E2;B2:E2;0)<4))/COUNTIF(RANK(B2:E2;B2:E2;0);"<4")}
returns the same result. But I think that it is not so clear and too clumsy.
It's not exactly the same formula, because if two (or more) equal values share the third position, your formula compute both in the average. I don't want that.
Ex. If now I put those values : 60 | 75 | 75 | 80 | 85, searching the average of the highest 3. My formula still returns 80, yours returns 78,75.
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-06-14 23:10:16 +0200
Seen: 143 times
Last updated: Mar 27
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.