Help with a calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-1690396%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1690396%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20to%20calculate%20the%20average%20of%20the%20lowest%208%20of%2020%20numbers.%3C%2FP%3E%3CP%3EE.g.%3C%2FP%3E%3CP%3E2%2C%204%2C%206%2C%208%2C%203%2C%2011%2C%209%2C%208%2C%203%2C%2010%2C%26nbsp%3B2%2C%204%2C%206%2C%208%2C%203%2C%2011%2C%209%2C%208%2C%203%2C%2010%2C%26nbsp%3B%20Answer%20is%203%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1690396%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1690547%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1690547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F801195%22%20target%3D%22_blank%22%3E%40Dave_Moyse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20numbers%20are%20in%20A2%3AA21.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20an%20ordinary%20formula%3A%3C%2FP%3E%0A%3CP%3E%3DAVERAGE(SMALL(A2%3AA21%2C%7B1%2C2%2C3%2C4%2C5%2C6%2C7%2C8%7D))%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%3DAVERAGEIF(A2%3AA21%2C%22%26lt%3B%3D%22%26amp%3BSMALL(A2%3AA21%2C8))%3C%2FP%3E%0A%3CP%3EAs%20an%20array%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%3DAVERAGE(SMALL(A2%3AA21%2CROW(1%3A8)))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I need a formula to calculate the average of the lowest 8 of 20 numbers.

E.g.

2, 4, 6, 8, 3, 11, 9, 8, 3, 10, 2, 4, 6, 8, 3, 11, 9, 8, 3, 10,  Answer is 3

 

4 Replies
Highlighted

@Dave_Moyse 

Let's say the numbers are in A2:A21.

 

As an ordinary formula:

=AVERAGE(SMALL(A2:A21,{1,2,3,4,5,6,7,8}))

or

=AVERAGEIF(A2:A21,"<="&SMALL(A2:A21,8))

As an array formula confirmed with Ctrl+Shift+Enter:

=AVERAGE(SMALL(A2:A21,ROW(1:8)))

Highlighted

@Dave_Moyse 

That could be

=AVERAGEIF(A1:A20,"<=" & SMALL(A1:A20,8))

if numbers are in A1:A20

Highlighted

@Hans Vogelaar

 

Thanks, all resoled. 

Highlighted

Thanks, all good.

@Sergei Baklan