Forum Discussion
Help with a calculation
I need a formula to calculate the average of the lowest 8 of 20 numbers. E.g. 2, 4, 6, 8, 3, 11, 9, 8, 3, 10, 2, 4, 6, 8, 3, 11, 9, 8, 3, 10, Answer is 3
Let's say the numbers are in A2:A21.
As an ordinary formula:
=AVERAGE(SMALL(A2:A21,{1,2,3,4,5,6,7,8}))
or
=AVERAGEIF(A2:A21,"<="&SMALL(A2:A21,8))
As an array formula confirmed with Ctrl+Shift+Enter:
=AVERAGE(SMALL(A2:A21,ROW(1:8)))
