If Statement Help

http://img368.imageshack.us/img368/2667/spread0yl.png

I'm trying to 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.

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.


Once I figure out how much I can win, the "Won $" and "Lost $" columns check the "Win" and "Loss" columns to figure out if I won. If I won, the "Won $" box will check the adjacent "Total Possible $" column and match that amount. If I lose, the "Lost $" box will check the adjacent "total possible $" column and match that amount.

I figured out how I can simplify this excel sheet by combining the "Won $" and "Lost $" columns. Examples 3 and 4 would result in a negative number. I could combine the the two columns into 1 column titled "net gain." If I win a bet, the number will be positive, and if I lose a bet the number will be negative. This approach should be neater and more simplified than the current 2 column system.

How would I combine the new "net gain" column with the "total possible $" column? How can I make an If statement that will work for the 4 different outcomes I listed above?

EDIT::

I found this example (below) to decide what grade someone recieves. If they score 89 or above they get an A, if they score 80-89 then they get a B, etc etc. Apparently you can do a bunch of if statements in each excel rectangle (what are the individual rectangles call?). I think I need to combine a bunch of if statements like this and use the AND function. I'll let everyone know how this works out.

=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))

Damn, this is the closest I came but it doesn't work.

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

[Meech]

That's waaay too complicated for this easy problem. I don't have time to do it right this minute, but...

I'd

- aggregate Loss$/Win$ into 1 column. Positive for win, negative for loss.

- Combine Win/Lose into 1 column. +1 for win, -1 for loss.

You can either directly calculate the win/loss$ with a lengthy IF statment, or you can calculate the potential win/loss$ in an intermediate column (like you have). You can always hide it if you don't want to see it.

[ QUOTE ]
That's waaay too complicated for this easy problem. I don't have time to do it right this minute, but...

I'd

- aggregate Loss$/Win$ into 1 column. Positive for win, negative for loss.

- Combine Win/Lose into 1 column. +1 for win, -1 for loss.

You can either directly calculate the win/loss$ with a lengthy IF statment, or you can calculate the potential win/loss$ in an intermediate column (like you have). You can always hide it if you don't want to see it.

[/ QUOTE ]

Yeah, it's definitely not easy. I'd prefer to not have the intermediate column. I'd rather have the lengthy if statement. Hoping I can figure this out.

[danzasmack]

do you know VBA at all? Adding a button and a form will make this a lot easier. I can help with that if you are interested.

[ QUOTE ]
do you know VBA at all? Adding a button and a form will make this a lot easier. I can help with that if you are interested.

[/ QUOTE ]

I am interested. I have minimal experience, but if you say it shouldn't be hard then i'll believe you.

[Meech]

VB is waaay overkill as well. This is very simple Excel expressions -- the type of thing Excel was meant to do.

To the OP:

Why is there both a gain and a loss on line 11 of your example?

[Meech]

Ok, try this:

In my sample spreadsheet, I have the following columns
A Units
B Wager
C Line
D Win
E Net

ABC are straighforward. Column D (Win) is a True/False column. Type in True if you won, false if you lost.

Under column E (Net) This will calculate the amount won/lost. The formula is:

=IF(D2,IF(C2<0,100/ABS(C2),C2/100)*B2,-IF(C2<0,100/ABS(C2),C2/100)*B2)

Give it a shot.

Alternatively, if you wanted to put +1 and -1 in the win column, the formula could be easier:

=IF(C2<0,100/ABS(C2),C2/100)*B2*D2

[ QUOTE ]
Ok, try this:

In my sample spreadsheet, I have the following columns
A Units
B Wager
C Line
D Win
E Net

ABC are straighforward. Column D (Win) is a True/False column. Type in True if you won, false if you lost.

Under column E (Net) This will calculate the amount won/lost. The formula is:

=IF(D2,IF(C2<0,100/ABS(C2),C2/100)*B2,-IF(C2<0,100/ABS(C2),C2/100)*B2)

Give it a shot.

Alternatively, if you wanted to put +1 and -1 in the win column, the formula could be easier:

=IF(C2<0,100/ABS(C2),C2/100)*B2*D2

[/ QUOTE ]

I might end up using that formula you listed at the bottom if I can't figure out how to get my solution to work. I'd prefer to add a sixth column titled "Loss" instead of putting either true/false and -1/+1 in a single column. I'd like to break it up into 2 columns.

I appreciate the help, class now.

[Meech]

Keep in mind you can always tally your wins and losses with a function even though they are in 1 column.

But if you want to keep wins in 1 column, and losses in another, try this:

Put this in Column E (Win$)

=IF(D2,IF(C2<0,100/ABS(C2),C2/100)*B2,0)

Put this in Column F (Loss$)

=IF(D2,0,IF(C2<0,100/ABS(C2),C2/100)*B2)

And use the TRUE/FALSE foor Column D (Win)

[ QUOTE ]
Ok, try this:

In my sample spreadsheet, I have the following columns
A Units
B Wager
C Line
D Win
E Net

ABC are straighforward. Column D (Win) is a True/False column. Type in True if you won, false if you lost.

Under column E (Net) This will calculate the amount won/lost. The formula is:

=IF(D2,IF(C2<0,100/ABS(C2),C2/100)*B2,-IF(C2<0,100/ABS(C2),C2/100)*B2)

Give it a shot.

Alternatively, if you wanted to put +1 and -1 in the win column, the formula could be easier:

=IF(C2<0,100/ABS(C2),C2/100)*B2*D2

[/ QUOTE ]

I think i'm going to use this formula you gave in the post before. One problem with it is that if I lose a bet, it still looks at the Line to see how much I lost when it should just look at the wager amount. The formula i'm talking about is the one you listed as the alternative.

Your false/true example does the same thing. If I bet $5 and lose when the line is +150 it will spit back than I lost $7.50.

EDIT: I fixed the false true one

=IF(D2,IF(C2<0,100/ABS(C2),C2/100)*B2,-IF(C2<0,B2,B2))

The problem is that if I lost the bet, your formula still had it look at the line to determine how much I lost.