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)
Posted by cpod on June 11, 2001 11:09 AM
This will give the sum of the last five columns in the first row:
=SUM(OFFSET(A1,0,COUNT(A1:BB1)-5,1,5))
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. : 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 Darren Smith on June 12, 2001 12:34 PM
Thanks I think its going to work
: : 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.
