formula for the average of the lowest 8

[janvandriel50]

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?

[#2]

Try: =AVERAGE(SMALL(INDIRECT("F3:F22"),ROW(INDIRECT("1:8"))))

[#3]

@janvandriel50 

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)

[#4]

@JMB17 

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

[#5]

@janvandriel50 

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