P
poncho
have a row of 8 numbers and want to average the top 5 numbers
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
T. Valko
Try this:
=AVERAGE(LARGE(A1:H1,{1,2,3,4,5}))
If there is less than 5 numbers in the range the formula will return an
error.
=AVERAGE(LARGE(A1:H1,{1,2,3,4,5}))
If there is less than 5 numbers in the range the formula will return an
error.
P
poncho
poncho said:have a row of 8 numbers and want to average the top 5 numbers
Thanks Bob Phillips, found it in an earlier thread...
P
poncho
Thanks T. Valko, needed to know about the error...
S
Shane Devenshire
Hi,
You could also consider
=SUMPRODUCT(LARGE(A1:H1,ROW(1:5))/5)
or to handle errors because of less than 5 numbers:
=AVERAGE(LARGE(A1:H1,ROW(INDIRECT("1:"&MIN(5,COUNT(A1:H1))))))
this will calculate the average of the 5 largest or the 4 largest if there
are only 4 items and so on. This formula requires array entry (press
Shif+Ctrl+Enter to enter it, not Enter.)
You could also consider
=SUMPRODUCT(LARGE(A1:H1,ROW(1:5))/5)
or to handle errors because of less than 5 numbers:
=AVERAGE(LARGE(A1:H1,ROW(INDIRECT("1:"&MIN(5,COUNT(A1:H1))))))
this will calculate the average of the 5 largest or the 4 largest if there
are only 4 items and so on. This formula requires array entry (press
Shif+Ctrl+Enter to enter it, not Enter.)