Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
I'm relatively new to excel and am trying to put a spreadsheet together for
golf game results. I am trying to give a cell a value that represents the net score for each player on each hole. Example: Hole #1 is a par 4 with a handicap of 12, the player has a handicap of 25 and scores a 5 on the hole. The net score for that hole should be the gross score minus 2, for a net 3. Basically, if the player's handicap is more than or equal to the hole handicap then the player's net score is the gross score minus 1. If the player's handicap is more than or equal to 2 times the hole handicap then the player's net score is the gross score minus 2. Everything I've tried so far hasn't worked. Help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
You haven't told us what formulae you've tried, and in what way they didn't
meet your requirements, so we can't help you to correct your formulae. Please remember next time to tell us the formula and what result it gives gor given inputs, and what result you had expected. To start from square one, if the handicap for the hole is in column C, the gross score for the player in column D, and the player's handicap in F1, then in E2 the formula for the net score could be either =IF(F$1=2*C2,D2-2,IF(F$1=C2,D2-1,D2)) or =D2-MIN(2,INT(F$1/C2)) -- David Biddulph "Mike" wrote in message ... I'm relatively new to excel and am trying to put a spreadsheet together for golf game results. I am trying to give a cell a value that represents the net score for each player on each hole. Example: Hole #1 is a par 4 with a handicap of 12, the player has a handicap of 25 and scores a 5 on the hole. The net score for that hole should be the gross score minus 2, for a net 3. Basically, if the player's handicap is more than or equal to the hole handicap then the player's net score is the gross score minus 1. If the player's handicap is more than or equal to 2 times the hole handicap then the player's net score is the gross score minus 2. Everything I've tried so far hasn't worked. Help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
David,
Typically a golf scorecard reads down. A1 Hole #1 a2 Par a3 4 a4 Hndcp a5 12 a6 Player Handcp a7 25 a8 Gross a9 5 a10 Net a11 3 In A11 I've entered the following; =IF(A7=A5*2,A9-2,A9-1) Works perfectly, but, a player should never receive a reduction in score where his handicap is LESS than the handicap for the hole. Can you add/adjust my formula to reflect a net score the same as the gross score. i.e. A7 is less than A5, then A11 would be the same as A9. I'm missing something! Thanks, Bob M. "David Biddulph" wrote: You haven't told us what formulae you've tried, and in what way they didn't meet your requirements, so we can't help you to correct your formulae. Please remember next time to tell us the formula and what result it gives gor given inputs, and what result you had expected. To start from square one, if the handicap for the hole is in column C, the gross score for the player in column D, and the player's handicap in F1, then in E2 the formula for the net score could be either =IF(F$1=2*C2,D2-2,IF(F$1=C2,D2-1,D2)) or =D2-MIN(2,INT(F$1/C2)) -- David Biddulph "Mike" wrote in message ... I'm relatively new to excel and am trying to put a spreadsheet together for golf game results. I am trying to give a cell a value that represents the net score for each player on each hole. Example: Hole #1 is a par 4 with a handicap of 12, the player has a handicap of 25 and scores a 5 on the hole. The net score for that hole should be the gross score minus 2, for a net 3. Basically, if the player's handicap is more than or equal to the hole handicap then the player's net score is the gross score minus 1. If the player's handicap is more than or equal to 2 times the hole handicap then the player's net score is the gross score minus 2. Everything I've tried so far hasn't worked. Help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
If you compare your layout with mine, and look at my formulae, then you need
to change my formula from =IF(F$1=2*C2,D2-2,IF(F$1=C2,D2-1,D2)) to =IF(A7=2*A5,A9-2,IF(A7=A5,A9-1,A9)), which you will see is similar to your formula but you had missed the second test. The alternative way would change my =D2-MIN(2,INT(F$1/C2)) to =A9-MIN(2,INT(A7/A5)) -- David Biddulph "robert morris" wrote in message ... David, Typically a golf scorecard reads down. A1 Hole #1 a2 Par a3 4 a4 Hndcp a5 12 a6 Player Handcp a7 25 a8 Gross a9 5 a10 Net a11 3 In A11 I've entered the following; =IF(A7=A5*2,A9-2,A9-1) Works perfectly, but, a player should never receive a reduction in score where his handicap is LESS than the handicap for the hole. Can you add/adjust my formula to reflect a net score the same as the gross score. i.e. A7 is less than A5, then A11 would be the same as A9. I'm missing something! "David Biddulph" wrote: You haven't told us what formulae you've tried, and in what way they didn't meet your requirements, so we can't help you to correct your formulae. Please remember next time to tell us the formula and what result it gives gor given inputs, and what result you had expected. To start from square one, if the handicap for the hole is in column C, the gross score for the player in column D, and the player's handicap in F1, then in E2 the formula for the net score could be either =IF(F$1=2*C2,D2-2,IF(F$1=C2,D2-1,D2)) or =D2-MIN(2,INT(F$1/C2)) -- David Biddulph "Mike" wrote in message ... I'm relatively new to excel and am trying to put a spreadsheet together for golf game results. I am trying to give a cell a value that represents the net score for each player on each hole. Example: Hole #1 is a par 4 with a handicap of 12, the player has a handicap of 25 and scores a 5 on the hole. The net score for that hole should be the gross score minus 2, for a net 3. Basically, if the player's handicap is more than or equal to the hole handicap then the player's net score is the gross score minus 1. If the player's handicap is more than or equal to 2 times the hole handicap then the player's net score is the gross score minus 2. Everything I've tried so far hasn't worked. Help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
David,
I used your last formula =A9-MIN(2,INT(A7/A5)) and it works perfectly. Mike should be able to use it. Bob M. "David Biddulph" wrote: If you compare your layout with mine, and look at my formulae, then you need to change my formula from =IF(F$1=2*C2,D2-2,IF(F$1=C2,D2-1,D2)) to =IF(A7=2*A5,A9-2,IF(A7=A5,A9-1,A9)), which you will see is similar to your formula but you had missed the second test. The alternative way would change my =D2-MIN(2,INT(F$1/C2)) to =A9-MIN(2,INT(A7/A5)) -- David Biddulph "robert morris" wrote in message ... David, Typically a golf scorecard reads down. A1 Hole #1 a2 Par a3 4 a4 Hndcp a5 12 a6 Player Handcp a7 25 a8 Gross a9 5 a10 Net a11 3 In A11 I've entered the following; =IF(A7=A5*2,A9-2,A9-1) Works perfectly, but, a player should never receive a reduction in score where his handicap is LESS than the handicap for the hole. Can you add/adjust my formula to reflect a net score the same as the gross score. i.e. A7 is less than A5, then A11 would be the same as A9. I'm missing something! "David Biddulph" wrote: You haven't told us what formulae you've tried, and in what way they didn't meet your requirements, so we can't help you to correct your formulae. Please remember next time to tell us the formula and what result it gives gor given inputs, and what result you had expected. To start from square one, if the handicap for the hole is in column C, the gross score for the player in column D, and the player's handicap in F1, then in E2 the formula for the net score could be either =IF(F$1=2*C2,D2-2,IF(F$1=C2,D2-1,D2)) or =D2-MIN(2,INT(F$1/C2)) -- David Biddulph "Mike" wrote in message ... I'm relatively new to excel and am trying to put a spreadsheet together for golf game results. I am trying to give a cell a value that represents the net score for each player on each hole. Example: Hole #1 is a par 4 with a handicap of 12, the player has a handicap of 25 and scores a 5 on the hole. The net score for that hole should be the gross score minus 2, for a net 3. Basically, if the player's handicap is more than or equal to the hole handicap then the player's net score is the gross score minus 1. If the player's handicap is more than or equal to 2 times the hole handicap then the player's net score is the gross score minus 2. Everything I've tried so far hasn't worked. Help! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Mike,
If you go to the last 2-3 postings between David and myself, you should be able to complete your project. Bob M. "Mike" wrote: I'm relatively new to excel and am trying to put a spreadsheet together for golf game results. I am trying to give a cell a value that represents the net score for each player on each hole. Example: Hole #1 is a par 4 with a handicap of 12, the player has a handicap of 25 and scores a 5 on the hole. The net score for that hole should be the gross score minus 2, for a net 3. Basically, if the player's handicap is more than or equal to the hole handicap then the player's net score is the gross score minus 1. If the player's handicap is more than or equal to 2 times the hole handicap then the player's net score is the gross score minus 2. Everything I've tried so far hasn't worked. Help! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
David,
Thank you so much for the help. I will try your solution later today and let you know how it worked. I have my spreadsheet set up accross but will adjust formula accordingly. Mike "David Biddulph" wrote: If you compare your layout with mine, and look at my formulae, then you need to change my formula from =IF(F$1=2*C2,D2-2,IF(F$1=C2,D2-1,D2)) to =IF(A7=2*A5,A9-2,IF(A7=A5,A9-1,A9)), which you will see is similar to your formula but you had missed the second test. The alternative way would change my =D2-MIN(2,INT(F$1/C2)) to =A9-MIN(2,INT(A7/A5)) -- David Biddulph "robert morris" wrote in message ... David, Typically a golf scorecard reads down. A1 Hole #1 a2 Par a3 4 a4 Hndcp a5 12 a6 Player Handcp a7 25 a8 Gross a9 5 a10 Net a11 3 In A11 I've entered the following; =IF(A7=A5*2,A9-2,A9-1) Works perfectly, but, a player should never receive a reduction in score where his handicap is LESS than the handicap for the hole. Can you add/adjust my formula to reflect a net score the same as the gross score. i.e. A7 is less than A5, then A11 would be the same as A9. I'm missing something! "David Biddulph" wrote: You haven't told us what formulae you've tried, and in what way they didn't meet your requirements, so we can't help you to correct your formulae. Please remember next time to tell us the formula and what result it gives gor given inputs, and what result you had expected. To start from square one, if the handicap for the hole is in column C, the gross score for the player in column D, and the player's handicap in F1, then in E2 the formula for the net score could be either =IF(F$1=2*C2,D2-2,IF(F$1=C2,D2-1,D2)) or =D2-MIN(2,INT(F$1/C2)) -- David Biddulph "Mike" wrote in message ... I'm relatively new to excel and am trying to put a spreadsheet together for golf game results. I am trying to give a cell a value that represents the net score for each player on each hole. Example: Hole #1 is a par 4 with a handicap of 12, the player has a handicap of 25 and scores a 5 on the hole. The net score for that hole should be the gross score minus 2, for a net 3. Basically, if the player's handicap is more than or equal to the hole handicap then the player's net score is the gross score minus 1. If the player's handicap is more than or equal to 2 times the hole handicap then the player's net score is the gross score minus 2. Everything I've tried so far hasn't worked. Help! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Bob,
Thank you and David for you help. I will try the ideas you sent me and let you know how it worked. Mike "robert morris" wrote: Mike, If you go to the last 2-3 postings between David and myself, you should be able to complete your project. Bob M. "Mike" wrote: I'm relatively new to excel and am trying to put a spreadsheet together for golf game results. I am trying to give a cell a value that represents the net score for each player on each hole. Example: Hole #1 is a par 4 with a handicap of 12, the player has a handicap of 25 and scores a 5 on the hole. The net score for that hole should be the gross score minus 2, for a net 3. Basically, if the player's handicap is more than or equal to the hole handicap then the player's net score is the gross score minus 1. If the player's handicap is more than or equal to 2 times the hole handicap then the player's net score is the gross score minus 2. Everything I've tried so far hasn't worked. Help! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Robert,
I sent the spreadsheet to your email address. Let me know what you think. I added some other stuff too. Thanks again, Mike "robert morris" wrote: Mike, I'm a little surprised to hear you say you have your spread sheet set up on rows (across). Do you then keep each hole in columns (down)? Almost every scorecard I have seen has the individual hole scores horizontally. Just curious. Would like to see your spreadsheet as I do some golf scoring. If you wouldn't mind, could you email a copy to me at . Bob M. "Mike" wrote: Bob, Thank you and David for you help. I will try the ideas you sent me and let you know how it worked. Mike "robert morris" wrote: Mike, If you go to the last 2-3 postings between David and myself, you should be able to complete your project. Bob M. "Mike" wrote: I'm relatively new to excel and am trying to put a spreadsheet together for golf game results. I am trying to give a cell a value that represents the net score for each player on each hole. Example: Hole #1 is a par 4 with a handicap of 12, the player has a handicap of 25 and scores a 5 on the hole. The net score for that hole should be the gross score minus 2, for a net 3. Basically, if the player's handicap is more than or equal to the hole handicap then the player's net score is the gross score minus 1. If the player's handicap is more than or equal to 2 times the hole handicap then the player's net score is the gross score minus 2. Everything I've tried so far hasn't worked. Help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|