Forum Discussion
I want to average the top 9 lowest scores in a column excluding zero
need a formula for this thank you
Hi Terry,
Please try this formula:
=AVERAGE(SMALL(IF(A1:A20=0, 9.99999999999999E+307,A1:A20),{1,2,3,4,5,6,7,8,9}))
NOTE: To enter this formula, you have to press Ctrl+Shift+Enter at the same time to force the formula to return the correct result, since this formula is a type of array formulas.
And you have to do this each time you open the formula in the edit mode.
Please find the attached file.
Regards
As a comment, to avoid CSE that could be
=AVERAGE(AGGREGATE(15,6,1/($A$1:$A$20<>0)*$A$1:$A$20,{1,2,3,4,5,6,7,8,9}))