Average highest 10 values or less

[Sonyak]

Hello

I am using average high row1:10 formula to get average of top 10 values in a row it works fine but some rows of values have less than 10 values and I'm getting errors

Any suggestions for formula to average top 10 values and in case there's less then average those values

Thankyou

[shg]

	a	b	c
1	1
2	2
3	3
4
5	5
6	6
7	7
8	8
9	9
10	10	5.6667	b10: {=average(iferror(large(a1:a10,{1,2,3,4,5,6,7,8,9,10}), false))}

[AliGW]

Welcome to the forum!

What formula are you currently using?

[Sonyak]

Thankyou I'm using formula below

{=average(large(b5:aq5,row(1:10)))}

[KOKOSEK]

Try this:

={AVERAGE(LARGE(IF(ISNUMBER(A1:C10),A1:C10),ROW(1:10)))}
put you range inside of course.

I did not test it so intensively.

[davsth]

=IF(COUNT(B5:aq5)<11,AVERAGE(B5:aq5),AVERAGE(LARGE(IF(ISNUMBER(B5:aq5),B5:aq5),ROW(1:10))))

Entered with shift control enter as it is an array

[Phuocam]

{=average(large(b5:aq5,row(1:10)))}

Try:

{=AVERAGE(LARGE(B5:AQ5,ROW(INDIRECT("1:"&MIN(10,COUNT(B5:AQ5))))))}