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 work.
The problem I have is that if I add another value in cell F23, shift everything up by 1 row, so new F23 becomes F22 etc etc and original F3 disappears, the formula changes and takes F21 as last value in stead of remaining F22.
how to solve this?
4 Replies
Perhaps the question is not how to calculate the average of smallest 8 in given range, but how to calculate it in dynamic range, isn't it?
Another question is what to consider under smallest. If we have {1,2,2,3,4} smallest two will be {1,2} or {1,2,2} ? In first case average is 1.5, in second one is 1.66667
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)
- Try: =AVERAGE(SMALL(INDIRECT("F3:F22"),ROW(INDIRECT("1:8"))))
Many thanks!! I've tried it several times, and it works. Now I have given me the task to see what this formula actually is doing. I haven't come across the INDIRECT in Excel yet.
Jan
