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

[patwcar]

It's for golf.

 

[chrisxxyyzz]

Can you sort the list?

 

[patwcar]

Yes, and I have created a formula to compute golf handicap on the 10 lowest (sorted) scores. I was just wondering if there is a formula so I don't need to sort.

 

[Bubbis Thedog]

=SUMIF($A$1:$A$20,"<="&SMALL($A$1:$A$20,10))/10

 

[Aladin Akyurek]

=SUMIF($A$1:$A$20,"<="&SMALL($A$1:$A$20,10))/10

That would include all of the instances of the 10th smallest value in the range. To average exactly/strictly 10 smallest values, invoke:

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

which you need to confirm with control+shift+enter.

 

[Bubbis Thedog]

Ah! Oversight on my behalf, Aladin. Thank you.

(Sometimes I learn more from my posts than I do from my questions! )

 

[patwcar]

Thanks for the help. The formula Bubbis provided works. So, perhaps being on the wagon is a good thing.

 

[Aladin Akyurek]

Thanks for the help. The formula Bubbis provided works. So, perhaps being on the wagon is a good thing.

That means you know what that formula involves and that's exactly what you want. Small example:

1
2
3
3
4

What is the average of 3 smallest values out of 5? 2.25 or 2?

And the point is not "works" vs "does not work". Rather, what is the calculation one needs.

 

[patwcar]

Didn't mean to offend. Your formula worked, it was simply not the calculation I wanted.

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

I don't know excel well enough to understand the formulas you both graciously provided. I simply cut and pasted them to see which worked. If you would like, I would be happy to send the spreadsheet, and the rules for handicapping, so you can check it out.

Again, thank you for the help.

 

[tracy28]

I already did this handicapping logic

I have already built a spreadsheet that calculates a golf handicap using these types of formulas (uses 10 lowest scores if 20 exist, fewer scores if fewer than 20 scores exist, etc.). If you're interested, I can e-mail it to you. Let me know.