Thread: Average of top 3 items in a list

I want to calculate the average of top 3 items in a list. Can someone help mw with this?

Thanks - I want the numbers in corresponding cells in another column to be added. is there a formula for the same?

Originally Posted by NBVC

Do you mean?

=SUM(LARGE(C1:C7,{1,2,3}))

No - in column F there are numbers corrsponding to the numbers in column C. i want to add only the corresponding numbers in column F.

Sounds like you want to find the largest 3 values in column A and average the corresponding values for those rows in column B. One possibility is

=AVERAGE(IF(A2:A100>=LARGE(A2:A100,3),B2:B100))

confirmed with CTRL+SHIFT+ENTER

....but that might average more than 3 values if you have ties in column A, e.g. if column A contains only these values

3,4,4,5,5

then the above would average 4 values, i.e. those associated with the 2 fours and 2 fives, is that how it should work?

Originally Posted by EsKay!

No - in column F there are numbers corrsponding to the numbers in column C. i want to add only the corresponding numbers in column F.

Then amend formula above to

=AVERAGE(IF(C2:C100>=LARGE(C2:C100,3),F2:F100))

adjust range lengths as required

Originally Posted by daddylonglegs

Sounds like you want to find the largest 3 values in column A and average the corresponding values for those rows in column B. One possibility is

=AVERAGE(IF(A2:A100>=LARGE(A2:A100,3),B2:B100))

confirmed with CTRL+SHIFT+ENTER

....but that might average more than 3 values if you have ties in column A, e.g. if column A contains only these values

3,4,4,5,5

then the above would average 4 values, i.e. those associated with the 2 fours and 2 fives, is that how it should work?

The question about average in column C is fine and I have the average on largest 3 items.. Subsequent to that I also want to add corresponding numbers in column F (only those numbers in column F which correspond to the highest 3 numbers in column C).