Golf comp calculation needed for handicaps - average and median formulas I think

[shaneb]

Hi All
I'm trying to find a formula that will calculate the following:
Out of 9 scores I want the formula to select the median 5 scores and then calculate the average of those 5 selected scores.
For example: scores of 58, 41, 29, 45, 33, 46, 42, 36, 37
I want the formula to select the 5 median scores - 36, 37, 41, 42, 45
And then calculate the average of those 5 scores.
I'm having trouble how I can select the 5 middle scores.
Any help would be appreciated
Cheers

 

[AndyPandy27]

Hi shaneb,

Assuming your 9 scores are in A1:A9, use the array formula:

=SUM(LARGE(A1:A9,{3,4,5,6,7}))/5

Make sure to enter this using CTRL+SHIFT+ENTER.

 

[Andrew Poulsom]

To avoid having to press Ctrl+Shift+Enter you can use:

=SUMPRODUCT(LARGE(A1:A9,{3,4,5,6,7}))/5

 

[shaneb]

Thanks AndyPandy27. I think that has given me what I am after. Can you just tell me what the "LARGE" function does?
Cheers
Shane

 

[shaneb]

Thanks Andrew.

 

[AndyPandy27]

Hi shaneb,

Absolutely - the Large function returns the k-th largest number in a data set.

The formula syntax is =Large(array,k)

Where array is the list of values you're choosing, and k is an integer which refers to which largest number you want to return; e.g. if k = 5, then the formula would return the 5th largest number in the array you specified.