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?
I’m a fan of the array calculation
=AVERAGE(LARGE(B2:E2,{1,2,3}))
which you have to enter with Ctrl-Shift-Enter, since it’s an array calculation. As an array calculation, it will appear like this
{=AVERAGE(LARGE(B2:E2,{1,2,3}))}
You can use SMALL for the smallest values, or SUM instead of AVERAGE. For example, when calculating student’s average scores when the lowest 3 grades out of 12 are dropped, I use
{=(SUM(B2:M2)-SUM(SMALL(B2:M2,{1,2,3}))/9}
(the outer curly brackets appear after entering with Ctrl-Shift-Enter).
If the number of smallest/largest values is large, or a variable depending on something else, you can use
{=SUM(LARGE(B1:B2000,ROW(OFFSET($A$1,0,0,A2))))}
where
ROW(OFFSET($A$1,0,0,A2))
is just a way to generate integers from 1 to the value in cell A2.
I learned something - thanks! 
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.