# Golf handicap revisted

Posted by Darren Smith on June 11, 2001 10:33 AM

Can someone take a look at this? I have had one response but I cannot get it to work. I am not new to excel but I would not consider myself an expert.

Well I dont know where to start. I am trying to build a spreadsheet for a golf league. I have names on the first column. I have handicap scores down the second column. Then the third column starts the golf scores. So say I have the name Jeff on A1, I have a space for a handicap score on A2, and golf scores on A3,A4,A5,A6,A7. I need to do a calculation to get the handicap, (A3:A7)-180)/5*.80. How I accomplished this is to do a formula on a cell far ahead IF(A7,"","",SUM(A3:A7)-180)/5*.80. But now the next time I add a score I only want the calculation to be performed on the last five scores, (A4:A8) I tried to do another calculation next the first IF one like IF(A8,"","",SUM(A4:A8)-180)/5*.80 then I do an average on all my IF cells and put that number in the A2 column (handicap) The problem is that I want to pre-configure all my IF cells so all I have to do is put in the scores each week. When I do this I get #value! in my IF cells when I dont have scores corresponding to that IF cell yet which messes up my handicap.

I'm venturing into unfamiliar territorty with this formula, but give this a try.

=SUM(OFFSET(A3,COUNT(Sheet1!$A$3:$A$99)-5,0,COUNT(Sheet1!$A$3:$A$99),1))-180/5*0.8

This is assuming you have the data in the cells you mention above... when I read your first sentence I thought the scores were going left to right, but your cell names lead me to believe they are going up and down.

Good luck (I am gone for the weekend)