pick top 5 out of 8 numbers
 

have a row of 8 numbers and want to average the top 5 numbers

pick top 5 out of 8 numbers
 

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.

--
Biff
Microsoft Excel MVP


"poncho" <u49547@uwe wrote in message news:919390a5827bd@uwe...
have a row of 8 numbers and want to average the top 5 numbers

pick top 5 out of 8 numbers
 

poncho wrote:
have a row of 8 numbers and want to average the top 5 numbers

Thanks Bob Phillips, found it in an earlier thread...

pick top 5 out of 8 numbers
 

Thanks T. Valko, needed to know about the error...

T. Valko wrote:
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.

have a row of 8 numbers and want to average the top 5 numbers

pick top 5 out of 8 numbers
 

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.)


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"poncho" wrote:

poncho wrote:
have a row of 8 numbers and want to average the top 5 numbers

Thanks Bob Phillips, found it in an earlier thread...