MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Golf handicap


Posted by Darren Smith on June 08, 2001 1:43 PM

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 have this spreadsheet at 216.222.23.65/da.xls
Can someone please take a look before I pull all my hair out....
Thanks
Darren


Posted by IML on June 08, 2001 3:20 PM

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)

Posted by Darren on June 09, 2001 1:15 PM


I need to wait till monday to try your formula, but thanks for the help