Ask Your Question

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

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


which you have to enter with Ctrl-Shift-Enter, since it's an array calculation. As an array calculation, it will appear like this


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


(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




is just a way to generate integers from 1 to the value in cell A2.

edit flag offensive delete link more



I learned something - thanks! :)

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

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

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


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.

edit flag offensive delete link more

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


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

Seen: 10,094 times

Last updated: Nov 16 '13