MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Golf PS

Posted by Darren Smith on May 07, 2001 2:34 PM

If I have five cells A1-A5 with five numbers in them (golf scores) and I do a calculation on these five cells to figure out a golf handicap it works great, but I want to be able to add a number to A6 and have the calculation figure out the handicap on A2-A6 now, how can I do this without redoing the calculation. I have been trying to do this with macros and a button but I cant seem to get it to work. Any help??

Posted by Barrie Davidson on May 07, 2001 2:42 PM

Hi, what is the calculation you perform to calculate the handicap (obviously I'm not a golfer)?


Posted by Darren Smith on May 07, 2001 2:55 PM

Neither am I, but its a calculation like all five numbers added up together than * a number I think, but lets go with the five numbers added up times 2. I can always change the calculation later. But I will be adding a new number all the time but I only want the calculation to be performed on the last five numbers. Like say I have a column of numbers A1-A10 I then have a calculation in say B1 of SUM(A6:A10)*2 When I put a new number in A11 I want the calculation in B1 to be SUM(A7:A11)*2 how do I get it to calculate the last five numbers automatically without having to redo the formula each time. Does this make any sense?

Posted by Barrie Davidson on May 07, 2001 3:00 PM

Okay Darren, here's a quick piece of code I wrote that should do the trick. It assumes your data is in column A, sums up the last 5, multiplies that total by 2, displays a message box telling you the handicap, and finally pastes the handicap value in cell B2.

Sub Handicap_Calculator()
Dim Handicap

On Error GoTo Error_Handler
ActiveCell.Offset(-4, 0).Range("A1:A5").Select
Handicap = Application.WorksheetFunction.Sum(Selection) * 2
MsgBox prompt:="Handicap = " & Handicap
Range("B1").Value = Handicap
Exit Sub
MsgBox prompt:="Error encountered calculating", _
End Sub

Let me know if this works for you.

Posted by Darren Smith on May 07, 2001 3:05 PM

giving me to much credit

Where do I type this code. I know very basic excel. I assume this is Visual Basic code but I dont know where to type it into..

Posted by Aladin Akyurek on May 07, 2001 3:46 PM

See: 16686.html