Excel - Golf Handicap - Episode 2165

Microsoft Excel Tutorial: How to calculate a Golf Handicap in Excel

Welcome back to another episode of the MrExcel podcast, where we dive into all things Excel. In today's episode, we will be discussing how to calculate your golf handicap using Excel. This topic was inspired by a question from Andrew, who attended one of my seminars in Fort Myers, Florida. He asked if there was a way to use Excel to figure out his golf handicap, and the answer is yes!

To start off, Andrew already had a column of all his golf scores in column A. Our goal is to find the average of the 10 smallest scores from the last 20 rounds of golf. The average part is easy, but the challenge lies in finding the second, third, and fourth smallest scores. This is where the SMALL function comes in handy. While the MIN function finds the smallest value, the SMALL function allows us to specify which smallest value we want to find.

To use the SMALL function, we need to create an array constant with the numbers 1 through 10. This can be done by typing 1 in a cell, holding down the Ctrl key, and dragging the fill handle to get the numbers 1 through 10. Then, we can use the F9 key to calculate and select the array constant. This array constant will be the second argument in our SMALL function, which will return 10 values at once. We can then use these values in the AVERAGE function to find the average of the 10 smallest scores.

Alternatively, we could manually type out the formula to find the average of the 10 smallest scores, but this would be time-consuming. That's why I prefer to use the array constant method, which is much faster and easier to understand. This is just one of the many Excel tips and tricks that you can find in my book, "POWER EXCEL with MrExcel", where I solve 617 Excel mysteries.

I want to thank Andrew for inspiring today's episode and for attending my seminar. And thank you for tuning in to another episode of the MrExcel podcast. If you enjoyed this video, don't forget to click the 'i' at the top right-hand corner to check out my book. And as always, I'll see you next time for another netcast from MrExcel.

#excel
#microsoft
#microsoftexcel
#exceltips
#excelformula
#excelformulasandfunctions
#evergreenhits

This video answers these common search terms:
excel golf handicap formula
calculating golf handicap using excel
golf handicap calculation spreadsheet
excel golf handicap calculator
golf handicap formula in excel
tracking golf handicap with excel

Table of Contents:
(00:00) Excel Calculate Golf Handicap
(00:14) Using MIN function to find smallest value
(00:24) Using SMALL function to find 10 smallest scores
(00:38) Finding second, third, and fourth smallest values
(00:48) Using Array constant for faster calculation
(01:55) Using Array constant to calculate Golf Handicap
(02:05) How to get numbers 1 through 10
(02:15) Creating an Array constant
(02:32) Using Array constant in SMALL function
(02:57) Sending 10 values into AVERAGE function
(03:18) Clicking Like really helps the algorithm

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

[ismailismaili]

i love the array way you showed thank you Mr. Bill

[percyarbulu]

Hello Bill, I'm curious. I've seen a podcast where you're referring to Alejandro Quiceno. Do you remember what it was?
Thank you.

[chrispinn]

Hi Bill, I have a stableford competition this week, I have been asked to compile the scores and data after the match. Is there an excel spreadsheet which shows:- Hole length, Par, Index, Player handicap which then totalises their stableford score/

[steve]

Hi, exactly what I was looking for but sometimes I only play 9 holes, thus there is a blank cell in the row, so how can you add arguments to ignore blank cells and to only count the last 20 values, not using an array of say 20 cells? I hope you can help.

[tomkemp]

Hi, how do you work out eclectic scoring on excel for 20 guys.???

[mikeklemesrud]

This was a lot of help. Thank you.
Question... How can we show the average of the ten lowest scores of the most recent 20 scores? And would like this to show in the just one cell, not a formula to drag down. Is this possible?

[naveedkhan]

Thanks for this wonderful illustration on working out one's handicap based on the last 20 rounds, by suggesting the array formula...Since i am maintaining handicap accounts of a group of friends, i found the formula most useful. However, I wonder if Bill has a solution to pickup the last 20 rounds from a continuous cycle of golf rounds, without having to delete the rounds in excess of the last 20..

[mokhtarsaeed]

we can use small with row function it will be easy and fast =SMALL(A2:A7;ROW()-2)

[franciscorivas]

Winner of a video, I have been researching "17 handicap golf" for a while now, and I think this has helped. Ever heard of - Tonayden Senadelyn Remedy - (do a search on google ) ? Ive heard some awesome things about it and my neighbour got great success with it.