Golf PS

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

Barrie

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

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

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,

See: 16686.html