First time here? Check out the FAQ!
asked 2012-06-14 23:10:16 +0200Beizerno
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?
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
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
returns the same result. But I think that it is not so clear and too clumsy.
answered 2013-03-25 14:55:07 +0200Beizerno
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