formula for the average of the lowest 8

%3CLINGO-SUB%20id%3D%22lingo-sub-2100670%22%20slang%3D%22en-US%22%3Eformula%20for%20the%20average%20of%20the%20lowest%208%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2100670%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%3D%2BAVERAGE(SMALL(%24F%243%3A%24F%2422%2CROW(%241%3A%248)))%20to%20calculate%20the%20average%20of%20the%20lowest%20eight%20in%20column%20F3%3AF22.%20I%20know%20I%20have%20to%20press%20ctrl%2Fshft%2Fenter%20to%20make%20the%20formula%20work.%3C%2FP%3E%3CP%3EThe%20problem%20I%20have%20is%20that%20if%20I%20add%20another%20value%20in%20cell%20F23%2C%20shift%20everything%20up%20by%201%20row%2C%20so%20new%20F23%20becomes%20F22%20etc%20etc%20and%20original%20F3%20disappears%2C%20the%20formula%20changes%20and%20takes%20F21%20as%20last%20value%20in%20stead%20of%20remaining%20F22.%3CBR%20%2F%3Ehow%20to%20solve%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2100670%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2100682%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20the%20average%20of%20the%20lowest%208%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2100682%22%20slang%3D%22en-US%22%3ETry%3A%20%3DAVERAGE(SMALL(INDIRECT(%22F3%3AF22%22)%2CROW(INDIRECT(%221%3A8%22))))%3C%2FLINGO-BODY%3E
New Contributor

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
Try: =AVERAGE(SMALL(INDIRECT("F3:F22"),ROW(INDIRECT("1:8"))))

@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)

@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

@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