How to calculate a Golf Handicap in Excel
You have your recent golf scores in Excel
The handicap is the average of the lowest 10 scores in the last 20 rounds
Getting the last 20 is easy thanks to Excel
Getting the smallest using MIN
But… to get the 2nd smallest through 10th smallest, use SMALL() function.
Slow but understandable: =SMALL(,1)+SMALL(,2)+SMALL(,3)…+SMALL(,10) divided by 10
Easier to enter: Array constant {1;2;3;4;5;6;7;8;9;10} as the second argument of SMALL
This will return all 10 values at once
Send that in to the AVERGE function: =AVERAGE(SMALL(A1:A20,{1;2;3;4;5;6;7;8;9;10}))
My fast way for typing the array constant: Type a 1 in a cell. Ctrl+Drag the fill handle from the 1 down to get 2 through 10.
Point a formula at the ten numbers and press F9, then Ctrl+C to copy
You have your recent golf scores in Excel
The handicap is the average of the lowest 10 scores in the last 20 rounds
Getting the last 20 is easy thanks to Excel
Getting the smallest using MIN
But… to get the 2nd smallest through 10th smallest, use SMALL() function.
Slow but understandable: =SMALL(,1)+SMALL(,2)+SMALL(,3)…+SMALL(,10) divided by 10
Easier to enter: Array constant {1;2;3;4;5;6;7;8;9;10} as the second argument of SMALL
This will return all 10 values at once
Send that in to the AVERGE function: =AVERAGE(SMALL(A1:A20,{1;2;3;4;5;6;7;8;9;10}))
My fast way for typing the array constant: Type a 1 in a cell. Ctrl+Drag the fill handle from the 1 down to get 2 through 10.
Point a formula at the ten numbers and press F9, then Ctrl+C to copy
Transcript of the video:
Learn Excel from MrExcel podcast, episode number 2165 Excel Golf Handicap Hey, welcome back to MrExcel netcast!
I am Bill Jelen.
I was doing a seminar down at Fort Myers, Florida and Andrew shows up in my session and said, "Hey, is there a way I can figure out my Golf Handicap using Excel?" He already has a column of all the Golf scores over there in column A, this is...
Here is the thing, we want to check the last 20 rounds of Golf and then find 10 smallest scores in the last 20 and check the average of that, all right.
So, the average piece is easy.
Even the last 20 piece is easy.
Let's just say that we add a MIN function here to find the smallest value.
As I copy that formula down, it's automatically going to adjust, all right, so it's always going to find the last 20th.
If the first one finds the last 20th, then they're all going to find the last 20 and then, the average is easy piece.
The, the part that's hard here though is figuring out the second smallest, the third smallest and the fourth smallest and so on, all right.
So, I want to introduce a cooler function here, called 'SMALL'.
MIN finds the smallest value but SMALL, says we want to find, in this case, the second smallest value.
So, while 89 is the smallest value up there, the second smallest value is 91.
What if there is a tie, it would do the right thing, so if we add, well actually, let's look at this one, so you, so you get further and...
if we had two that were 89, the smallest would be 89, the second smallest would be 89.
All right, so it's going to work really well here.
And so, of course, it would be tempting, although time consuming to write all the formulas and it says, all right, we want the first smallest plus the second smallest plus the third smallest, fourth, fifth, sixth, all the way and then divide that whole thing by 10, like that, all right and just double click to copy that down and you'll see how your Golf Handicap changes over time.
Let's just put together a string here, where we put some really good Golf scores like 88, all in a row and you'll see that, sure enough, the Golf Handicap comes down, all right.
But, this would be time consuming to type that.
Well, it's easy to understand, time consuming, so or I rather use is a Array constant.
That's where we pass the SMALL function, all 10 numbers all at once.
And the way that I do this is, I just start a little formula here and I point to the numbers 1 through 10.
How do you get the numbers from 1 through 10, well that's easy!
Put the number 1 in, hold down the Ctrl key, where you grab the fill-handle and drag and you get the numbers 1 through 10 and just anywhere, equal point to those 10 cells but don't press Enter, instead press F9, to calculate and it selects, this is called an 'Array Consoment--Constant'.
So, ope, close your bracket, 1;2; and so on.
I can never remember that syntax, I always put the numbers 1 through 10 and then CTRL+C to copy that to the clipboard, press Escape.
Then, over here, we're with the SMALL functions, asking me for the smallest to 1.
So, like that, number 1 and paste.
And now, we're doing, it's asking for the smallest 10.
This SMALL function is going to return ten answers which is amazing.
And then send the whole thing in to the AVERAGE function, like that, double click to copy it down and it will work, all right.
Those are kinds of trick in this book 'POWER EXCEL with MrExcel".
617 Excel mysteries solved.
If you like this video, click at 'i' at top right-hand corner, take a look at buying that book.
Wrap-up today's episode, we have to calculate the Golf Handicap in Excel, so you get a list of your recent Golf scores in Excel.
The Handicapped is the lowest 10 of the last 20 rounds.
All right, the last 20 is easy, thanks to Excel, we just got to row 20 and points at the last 20 and you can even get the smallest using MIN.
But you get the 2nd smallest through the 10th smallest, we can use the SMALL function.
So, it would be slow but understandable, ask for the =SMALL(,1)+SMALL(,2)+SMALL(,3) and whole thing divided by 10.
But, instead I am using an Array constant, with all the numbers 1 through 10, separated by semi-colons, inside the closed brackets and that's going to become the second argument of SMALL.
This is going to return 10 values all at once.
Send those 10 values into the AVERAGE function.
And then, my fast way of typing the Array constant.
First put a number 1 in, CTRL, drag and I'll get the numbers from 1 through 10.
And then, point a formula at those ten numbers and press F9, CTRL+C to copy the Array constant under the clipboard and then you can paste it into your formula.
All right, here we thank Andrew for showing up for my seminar.
And I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
I was doing a seminar down at Fort Myers, Florida and Andrew shows up in my session and said, "Hey, is there a way I can figure out my Golf Handicap using Excel?" He already has a column of all the Golf scores over there in column A, this is...
Here is the thing, we want to check the last 20 rounds of Golf and then find 10 smallest scores in the last 20 and check the average of that, all right.
So, the average piece is easy.
Even the last 20 piece is easy.
Let's just say that we add a MIN function here to find the smallest value.
As I copy that formula down, it's automatically going to adjust, all right, so it's always going to find the last 20th.
If the first one finds the last 20th, then they're all going to find the last 20 and then, the average is easy piece.
The, the part that's hard here though is figuring out the second smallest, the third smallest and the fourth smallest and so on, all right.
So, I want to introduce a cooler function here, called 'SMALL'.
MIN finds the smallest value but SMALL, says we want to find, in this case, the second smallest value.
So, while 89 is the smallest value up there, the second smallest value is 91.
What if there is a tie, it would do the right thing, so if we add, well actually, let's look at this one, so you, so you get further and...
if we had two that were 89, the smallest would be 89, the second smallest would be 89.
All right, so it's going to work really well here.
And so, of course, it would be tempting, although time consuming to write all the formulas and it says, all right, we want the first smallest plus the second smallest plus the third smallest, fourth, fifth, sixth, all the way and then divide that whole thing by 10, like that, all right and just double click to copy that down and you'll see how your Golf Handicap changes over time.
Let's just put together a string here, where we put some really good Golf scores like 88, all in a row and you'll see that, sure enough, the Golf Handicap comes down, all right.
But, this would be time consuming to type that.
Well, it's easy to understand, time consuming, so or I rather use is a Array constant.
That's where we pass the SMALL function, all 10 numbers all at once.
And the way that I do this is, I just start a little formula here and I point to the numbers 1 through 10.
How do you get the numbers from 1 through 10, well that's easy!
Put the number 1 in, hold down the Ctrl key, where you grab the fill-handle and drag and you get the numbers 1 through 10 and just anywhere, equal point to those 10 cells but don't press Enter, instead press F9, to calculate and it selects, this is called an 'Array Consoment--Constant'.
So, ope, close your bracket, 1;2; and so on.
I can never remember that syntax, I always put the numbers 1 through 10 and then CTRL+C to copy that to the clipboard, press Escape.
Then, over here, we're with the SMALL functions, asking me for the smallest to 1.
So, like that, number 1 and paste.
And now, we're doing, it's asking for the smallest 10.
This SMALL function is going to return ten answers which is amazing.
And then send the whole thing in to the AVERAGE function, like that, double click to copy it down and it will work, all right.
Those are kinds of trick in this book 'POWER EXCEL with MrExcel".
617 Excel mysteries solved.
If you like this video, click at 'i' at top right-hand corner, take a look at buying that book.
Wrap-up today's episode, we have to calculate the Golf Handicap in Excel, so you get a list of your recent Golf scores in Excel.
The Handicapped is the lowest 10 of the last 20 rounds.
All right, the last 20 is easy, thanks to Excel, we just got to row 20 and points at the last 20 and you can even get the smallest using MIN.
But you get the 2nd smallest through the 10th smallest, we can use the SMALL function.
So, it would be slow but understandable, ask for the =SMALL(,1)+SMALL(,2)+SMALL(,3) and whole thing divided by 10.
But, instead I am using an Array constant, with all the numbers 1 through 10, separated by semi-colons, inside the closed brackets and that's going to become the second argument of SMALL.
This is going to return 10 values all at once.
Send those 10 values into the AVERAGE function.
And then, my fast way of typing the Array constant.
First put a number 1 in, CTRL, drag and I'll get the numbers from 1 through 10.
And then, point a formula at those ten numbers and press F9, CTRL+C to copy the Array constant under the clipboard and then you can paste it into your formula.
All right, here we thank Andrew for showing up for my seminar.
And I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
