Forum Discussion
formula for the average of the lowest 8
I am using the following formula: =+AVERAGE(SMALL($F$3:$F$22,ROW($1:$8))) to calculate the average of the lowest eight in column F3:F22. I know I have to press ctrl/shft/enter to make the formula wo...
An array of 8 values is small enough to allow the use of an array constant
= AVERAGE(SMALL(values,{1;2;3;4;5;6;7;8}))
To obtain this, use one of the suggested formulas and use F9 to evaluate the sequence within the formula bar.
That may avoid the need for CSE though I cannot check since I use Excel 365. The way in which I used to avoid CSE was to place the array calculation within a defined name, since it is only on the grid that array formulas get messed up.
smallest8values: =SMALL(values,{1;2;3;4;5;6;7;8})
= AVERAGE(smallest8values)