Jan 26 2021 09:03 PM
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?
Jan 26 2021 09:14 PM
Jan 26 2021 11:38 PM
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)
Jan 26 2021 11:47 PM
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
Jan 27 2021 09:31 AM
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