I need help with a tough IF statement in Excel

levistep · Nov 17, 2005

I'm trying to optimize my excel spreadsheet for my sports wagers. Column A denotes the date of the bet, Column B is the team I bet on, Column C is how much I wagered, Column D is how much I could possibly win from the bet, Column E is how much I won, if I won, from the bet, Column F is how much I lost, if I lost, from the bet, Columns G & H are where I tally my wins and losses, and Column I is the line I got when I made the bet.

How do I get rid of that "Total Possible $" column? The reason why I need it is because there are 4 possible outcomes that decide how much money I win on a certain bet and I can't figure out the correct IF statement. The "Total Possible $" column acts as the auxiliary column that does the calculation, and then the "Won $" and "Lost $" columns check to see if I won the bet. If I won the bet, the amount in the "Total Possible $" column is entered in the "Won $" column and if I lost, the amount in the "Total Possible $" column is entered in the "Lost $" column.

The reason why there is a problem is because of the way sports books set their lines. Sometimes the line is positive, and sometimes the line is negative. This causes the need for twice as many formulas. The following formulas need to be used in the IF statement-

1) If I bet on a line that is negative and win (IE: -220, Red Sox, August 23) then the formula (100/-L4)*D4 is used to figure out how much I won.

2) If I bet on a line that is positive and win (IE: +150, Braves, August 24) then the formula is (L6/100)*D6 is used to figure out how much I won.

3) If I bet on a line that is negative and lose (IE: -101, Astros, August 23) then the formula is -((L5/100)*D5) is used to figure out how much I lost.

4) If I bet on a line that is positive and lose (IE: 147, Cardinals, August 26) then the formula -((100/-L4)*D4) is used to figure out how much I lost.

Does anyone have the knowledge to help me with this? This is the closest I've gotten.

=IF(AND(L9>=100,I9=1),((L9/100)*D9)),IF(AND(L9>=100,I9=0),(-((100/-L9)*D9))),IF(AND(L9<100,I9=1),((100/-L9)*D9)),IF(AND(L9<100,I9=0),(-((100/-L9)*D9)))

lander · Nov 17, 2005

I made a seperate column that converted the mathmatically inept American odds to decimal. The calculations are much cleaner that way. you can simply hide the column if it gets too messy.

levistep · Nov 17, 2005

lander said:
I made a seperate column that converted the mathmatically inept American odds to decimal. The calculations are much cleaner that way. you can simply hide the column if it gets too messy.

Thanks for the input. I may eventually try that, but I'd still like to know if anyone can figure out the correct IF statement, if possible. I posted my problem at another forum, and the only response was similar to what you said. Maybe the statement isn't possible.

JOH · Nov 17, 2005

Hope this helps...

I don't keep track of wins and losses or what teams I play but this is what I do:

I use four columns:

Code:

Wager	Line	Winnings	Losses
			
A3	B3	C3	D3
A4	B4	C4	D4
A5	B5	C5	D5
A6	B6	C6	D6

Let's say the wager in cell A3 is $168.

For B3, say the line is -168.

For C3, enter the function =IF(B3<0,A3*(100/-B3),A3*(B3/100)).

If the game is a loss, I'm not really concerned with what the line was so I go back and make the line = 0 in cell B3 and then,

For D3, the function is =IF(B3=0,-A3,0)

This eliminates the need for the extra column for Possible Winnings.

Pretty simple way to keep track of it, but it all depends on how much information you want to save.

Btw, have you used this imageshack long. Been looking for a free way to convert .xls to .jpg. Thanks.