Average highest/lowest values [closed]

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?

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-11-14 14:57:24.411147

Sort by » oldest newest most voted

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.

more

1 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.

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

more

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.

more

Stats

Asked: 2012-06-14 23:10:16 +0200

Seen: 9,396 times

Last updated: Nov 16 '13