Formula for tracking Nassau golf bets

B

Barney

I'm trying to track a golfing Nassau bets between two teams.

In columns B & C are the two teams respective scores on each hole. Column D
tracks how Team 1 stands for Bet #1. If Team 1 has the highest score on
hole 1, they are -1, if they are lowest on hole 1 they are +1, and if they
tie, they are 0 (zero). Each hole then increments column D by one, up or
down, or remains the same depending on the outcome of that hole.

Once a team is up or down by 2 a new bet (Bet 2) starts and is tracked in
column E. (Etc. to Bets 3, 4 and perhaps 5.)

Bet two, once started should increment just like bet 1 with each hole.

That is where my trouble starts as you can see in the example.

http://jaxsi.org/NassauBets.xls

My formula:

=IF(AND(SUM(E$5:E$6=0),ABS(D6)>1),IF($B7>$C7,E6-1,IF($B7=$C7,$E6,IF($B7<$C7,$E6+1,0))))

Can anyone suggest the correct formula?

I hope my description makes sense,

Thanks,

Barney
 
M

muddan madhu

may be this ?

close the bracket for sum function then give condition

=IF(AND(SUM(E$5:E6)=0,ABS(D6)>1),IF($B7>$C7,E6-1,IF($B7=$C7,$E6,IF($B7<
$C7,$E6+1,0))),0)
 
M

muddan madhu

sum(e5:e6) is not equal to zero and abs(D6) is equal to 1.
Both the condition are false in your formula, and u have not mentioned
what should be answer if the condition is false.

For that reason excel displays as false instead of result.
 
B

Barney

That is the problem, I don't know how to write that formula. Once I get a
"1" or a "-1" in column E, I want it to start incrementing just like column
D does. And it, column E, should continue to increment, up or down, until
it reaches row 13.

Note: Column E should not start to increment until column D gets to +2 or -
2. That may never occure.

Barney



sum(e5:e6) is not equal to zero and abs(D6) is equal to 1.
Both the condition are false in your formula, and u have not mentioned
what should be answer if the condition is false.

For that reason excel displays as false instead of result.
 
M

muddan madhu

bit confused

try this

=IF(AND(SUM(E$5:E6)=0,ABS(D6)>1),IF($B7>$C7,E6-1,IF(B7<C7,E6+1,E6)),IF
(ABS(IF($B7>$C7,E6-1,IF(B7<C7,E6+1,E6)))=2,0,IF($B7>$C7,E6-1,IF
(B7<C7,E6+1,E6))))
 
B

Barney

When I put that in I get "The formula you typed contains an error"

I don't see it but it must be there.

Barney



bit confused

try this

=IF(AND(SUM(E$5:E6)=0,ABS(D6)>1),IF($B7>$C7,E6-1,IF(B7<C7,E6+1,E6)),IF
(ABS(IF($B7>$C7,E6-1,IF(B7<C7,E6+1,E6)))=2,0,IF($B7>$C7,E6-1,IF
(B7<C7,E6+1,E6))))
 
G

Greg Wilson

I have it as syntactically correct. I think it's likely a copy/paste error on
your part. Make sure there are no gaps between the IF's and the parentheses
that follow; e.g. should be "IF(B7<C7" instead of "IF (B7<C7".

Greg
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top