Want 1st 10 non blank entries in a row

[Barry_D]

I have been searching for some way to operate on the 1st 10 number entries in a row. The row may contain blanks or numbers. 10 number entries could be in the 1st 10 cells or it may take 50 or more cells to find the 1st 10 numbers. I want to use the result in a formula, specifically to find the average of the highest 5 numbers out of the 1st 10.

The formula: =average(large('range',{1,2,3,4,5}))

Thanks for any help or suggestions.

 

[Eric W]

Welcome to the forum.

How about:

=AVERAGE(LARGE(A2:INDEX(A2:Z2,SMALL(IF(ISNUMBER(A2:Z2),COLUMN(A2:Z2)-COLUMN(A2)+1),10)),{1,2,3,4,5}))

with Control+Shift+Enter. That's if your maximum range is A2:Z2. If you have the values in a column, you'd need to change the 2 COLUMN functions to ROW.

 

[Barry_D]

Welcome to the forum.

How about:

=AVERAGE(LARGE(A2:INDEX(A2:Z2,SMALL(IF(ISNUMBER(A2:Z2),COLUMN(A2:Z2)-COLUMN(A2)+1),10)),{1,2,3,4,5}))

with Control+Shift+Enter. That's if your maximum range is A2:Z2. If you have the values in a column, you'd need to change the 2 COLUMN functions to ROW.

That works so thank you very much. I have been fumbling all around this solution and finally you have solved it.

 

[Barry_D]

Oh, and there is a caveat. You'll get an error if there are LESS than 10 entries.