Ask Your Question
0

Average highest/lowest values [closed]

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

Beizerno gravatar image

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 flag offensive 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

4 Answers

Sort by » oldest newest most voted
3

answered 2013-11-16 21:35:51 +0200

DaveDixon gravatar image

updated 2013-11-16 21:37:54 +0200

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.

edit flag offensive delete link more

Comments

1

I learned something - thanks! :)

David gravatar imageDavid ( 2013-11-16 21:57:22 +0200 )edit
1

answered 2012-06-15 06:58:24 +0200

JohnSUN gravatar image

updated 2012-06-15 07:16:47 +0200

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.

edit flag offensive delete link more
1

answered 2013-03-26 14:33:33 +0200

JohnSUN gravatar image

updated 2013-03-27 08:40:35 +0200

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

edit flag offensive delete link more
0

answered 2013-03-25 14:55:07 +0200

Beizerno gravatar image

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.

edit flag offensive delete link more

Question Tools

Stats

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

Seen: 9,398 times

Last updated: Nov 16 '13