How do you average the 10 lowest numbers from a list of 20?

[patwcar]

It's for golf.

 

[Aladin Akyurek]

Didn't mean to offend.

"Offense", even if meant, is a secondary issue to me. "Works" here can be read by the third parties as "this formula works, that formula not", thereby thwarting their understanding the formulas.

Your formula worked, it was simply not the calculation I wanted.

The answer, in the context I need, to your question is 2.

...

If 2 is the answer you require, you have to switch to the formula with INDIRECT...

 

[tusharm]

Then, you should reexamine your belief that the SUMIF formula works. Try it with 1,2,3,4,5,6,7,8,9,10,10,11. If *any* number in the bottom 10 is repeated, every occurence of that number is included in the SUM but the denominator is not adjusted. The result is...well, I don't know what...but it sure does not satisfy the definition of an average.

{snip}

The answer, in the context I need, to your question is 2.

{snip}

 

[patwcar]

Yes, please send. Thanks,

patwcar AT yahoo DOT com

 

[Bubbis Thedog]

...the denominator is not adjusted.

They're right, pat. It was a knucklehead mistake on my part, neglecting the possibility of nonunique entries in your range. Are you understanding what the problem is with my initial suggestion?

 

[patwcar]

Yes, I understand the nature of the problem. To restate, if identical numbers exist and extend the total number of numbers to be more than 10, you can't get an accurate average.

So, if the other formula is correct, I am not installing it correctly because I get the wrong response. What does confirm with control+shift+enter mean/do.

 

[Aladin Akyurek]

Yes, I understand the nature of the problem. To restate, if identical numbers exist and extend the total number of numbers to be more than 10, you can't get an accurate average.

So, if the other formula is correct, I am not installing it correctly because I get the wrong response. What does confirm with control+shift+enter mean/do.

It's a so-called array formula. Such formulas must be confirmed by hitting the combination of control+shift+enter, This combination signals Excel to treat the formula as such.

aaAverageStrictlyNsmallestWithINDIRECT patwcar.xls
	A	B	C	D
1
2	42		35.3
3	42
4	39
5	39
6	42
7	38
8	43
9	36
10	31
11	45
12	42
13	34
14	38
15	45
16	31
17	31
18	38
19	41
20	37
Sheet1

C2:

=AVERAGE(SMALL(A1:A20,ROW(INDIRECT("1:"&MIN(10,COUNT(A1:A20))))))

If you properly apply control+shift+enter, you'll see braces around the formula appear on the Formula Bar.

 

[patwcar]

It gave me the result I want. Thank you for your patience.