# Average highest/lowest values

Beizerno
44 1 2 5

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?

delete close retag edit

Sort by » oldest newest most voted

JohnSUN
2358 2 23 37
http://wmstrong.ru/

Why you weren't warned that the values ​​can be repeated? OK, try this solution

JohnSUN
2358 2 23 37
http://wmstrong.ru/

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.

Beizerno
44 1 2 5

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.

## Donate

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!