Formula for tracking Nassau golf bets

[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

 

[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)

 

[Barney]

Except now I get FALSE in E8:E13.

See http://jaxsi.org/NassauBets.xls.

Barney

 

[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.

 

[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.

 

[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))))

 

[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))))

 

[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

 

[Barney]

Here is what I finally got to work:

=IF(OR(ABS(LARGE(E$5:E6,1)>1),ABS(SMALL(E$5:E6,1)<-1)),IF($C7<$D7,F6+1,IF($C7>$D7,F6-1,F6)),F6)

Check it out here: http://jaxsi.org/NassauBets.xls

Thanks to all for their help,

Barney