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??
Thanks


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)?

Barrie

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?
Thanks

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
Range("A1").End(xlDown).Activate
ActiveCell.Offset(-4, 0).Range("A1:A5").Select
Handicap = Application.WorksheetFunction.Sum(Selection) * 2
MsgBox prompt:="Handicap = " & Handicap
Range("B1").Value = Handicap
Range("B1").Select
Exit Sub
Error_Handler:
MsgBox prompt:="Error encountered calculating", _
Buttons:=vbCritical
End Sub


Let me know if this works for you.
Barrie

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..
Thanks,

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

See: 16686.html