formula help (selecting 10 fields of a selection of 20 and avg)

[mgp5013]

Basically like the title says, I am looking for a function to select the lowest 10 numbers in a given field and average them together. All numbers are listed in the A column from fields A1 to A20.

(Skip this part if you have a short attention span and just read the last paragraph)

That's the short version. Here is the detailed version for the ones who don't have ADD. In a nutshell, I am involved in the score keeping for an annual golf tournament. It is a handicapped event to keep everyone on an even playing field. We record all the scores for the entire history of the event. When you are dealing with the amount of data I am dealing with, its a royal pain in the *** doing all this manually. I am just doing a gross handicap and nothing too detailed. All I am looking at is the gross score and not dealing with any slope or course rating (that part was thrown in there for those who understand golf). So to keep this simple, we calculate a handicap by taking the 10 best scores out of the last 20 and averaging them together.

I am relatively proficient with excel and tried playing around with median, if, and average functions (and many combinations of the 3) but I'm at a loss. If you like tackling projects like this, please help me out.

 

[njimack]

Excel Workbook
	A	B	C
1	1	*	5.5
2	2	*	*
3	3	*	*
4	4	*	*
5	5	*	*
6	6	*	*
7	7	*	*
8	8	*	*
9	9	*	*
10	10	*	*
11	11	*	*
12	12	*	*
13	13	*	*
14	14	*	*
15	15	*	*
16	16	*	*
17	17	*	*
18	18	*	*
19	19	*	*
20	20	*	*
Sheet1

 

[mgp5013]

I am getting a #VALUE error after inputting the above formula.

 

[njimack]

Did you confirm with ctrl+shift+enter?

 

[mgp5013]

I love you. I have never heard of/used the small function before but in this scenario, it will definitely come in handy. Thank you very much.