CODEWITHSUNDEEP
excel
Echetlaeus
Echetlaeus

Reputation: 343

Using a subset of excel entries for calculation

Assume that you have 5 cells with values:

[12, 23, 50, 89, 95]

and you are interested in finding the average of the four largest entries (that is drop 12 because it is the smallest).

I wonder how one can do that in excel?

Upvotes: 2

Views: 76

Answers (3)

barry houdini
barry houdini

Reputation: 46371

You can get the average of the largest 4 from 5 with this formula

=AVERAGE(LARGE(A1:E1,{1,2,3,4}))

that will only average 4 values even if there are duplicates

Generically if you might have a variable number of values then to average without the smallest value you can use this version

=(SUM(Range)-MIN(Range))/(COUNT(Range)-1)

again that will work OK with duplicates - of course there must be at least 2 numbers in the range

Upvotes: 2

ShellFish
ShellFish

Reputation: 4551

Use =LARGE to get the ǹ-th largest value then use =SUMIF to add if the value is larger than the n-th value!

In pseudocode something like this: =SUMIF(data >= LARGE(range, n))/n, sorry it's been a while since I used excel. `

Upvotes: 0

JustAPup
JustAPup

Reputation: 1780

You can use AVERAGEIF(range,condition)

So in your case, it will be AVERAGEIF(A1:E1,">"&MIN(A1:E1))

Hope this helps..

Upvotes: 1

Related Questions