Odds Formula

[Remy Raven]

I'm creating a spreadsheet to keep track of my hobby, sports betting. Of course, being a mediocre Excel nerd, I want to automate as much as possible, but now I'm running into a formula issue. I swear I'm so close, but now I need help from the pros. So here's what I have and what I'm looking for:
A1 B1 C1 D1
$5.00 -110 W Formula

So I worked out the math - if you have to bet $1.10 to win $1, in this example, if I won this bet, I would win $4.50. To get to that answer, you divide 5 by 1.1. So here's the formula I wrote:
=IF(C1="W","" & if(B1<0,A1/(B1/-100)))

Since the line is a negative number, I divide by -100 to get the positive answer. But this isn't perfect and I can't edit the format to round or be currency. PLUS I want to add to this formula overall. If I type in "Even" in C1, if I bet $5 I win $5, so that's easy enough. But if the number is positive in C1, then A1 needs to multiply by B1/100.

I hope this is making sense. I'm stuck. I guess I could try to find the answer on a betting website or something, but I'd rather ask you guys and actually learn how to do it with your help.

 

[shg]

Is this what you're looking for?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Bet​

[/td][td="bgcolor:#F3F3F3"]

Moneyline​

[/td][td="bgcolor:#F3F3F3"]

Pays​

[/td][td="bgcolor:#F3F3F3"]

Profit​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

$ 100.00​

[/td][td]

-200​

[/td][td="bgcolor:#CCFFCC"]

$ 150.00​

[/td][td="bgcolor:#CCECFF"]

$ 50.00​

[/td][td="bgcolor:#CCFFCC"]C2: =A2 * IF(B2 <= -100, 1 - 100/B2, IF(B2 >= 100, 1 + B2/100))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

$ 100.00​

[/td][td]

-180​

[/td][td="bgcolor:#CCFFCC"]

$ 155.56​

[/td][td="bgcolor:#CCECFF"]

$ 55.56​

[/td][td="bgcolor:#CCECFF"]D2: =C2 - A2[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

$ 100.00​

[/td][td]

-160​

[/td][td="bgcolor:#CCFFCC"]

$ 162.50​

[/td][td="bgcolor:#CCECFF"]

$ 62.50​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

$ 100.00​

[/td][td]

-140​

[/td][td="bgcolor:#CCFFCC"]

$ 171.43​

[/td][td="bgcolor:#CCECFF"]

$ 71.43​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

$ 100.00​

[/td][td]

-120​

[/td][td="bgcolor:#CCFFCC"]

$ 183.33​

[/td][td="bgcolor:#CCECFF"]

$ 83.33​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

$ 100.00​

[/td][td]

-100​

[/td][td="bgcolor:#CCFFCC"]

$ 200.00​

[/td][td="bgcolor:#CCECFF"]

$ 100.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

$ 100.00​

[/td][td]

100​

[/td][td="bgcolor:#CCFFCC"]

$ 200.00​

[/td][td="bgcolor:#CCECFF"]

$ 100.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

$ 100.00​

[/td][td]

120​

[/td][td="bgcolor:#CCFFCC"]

$ 220.00​

[/td][td="bgcolor:#CCECFF"]

$ 120.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

$ 100.00​

[/td][td]

140​

[/td][td="bgcolor:#CCFFCC"]

$ 240.00​

[/td][td="bgcolor:#CCECFF"]

$ 140.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

$ 100.00​

[/td][td]

160​

[/td][td="bgcolor:#CCFFCC"]

$ 260.00​

[/td][td="bgcolor:#CCECFF"]

$ 160.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

$ 100.00​

[/td][td]

180​

[/td][td="bgcolor:#CCFFCC"]

$ 280.00​

[/td][td="bgcolor:#CCECFF"]

$ 180.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

$ 100.00​

[/td][td]

200​

[/td][td="bgcolor:#CCFFCC"]

$ 300.00​

[/td][td="bgcolor:#CCECFF"]

$ 200.00​

[/td][td][/td][/tr]
[/table]

 

[Remy Raven]

That looks like it will work. Thanks! Since I'm looking for just the profit part, I added -A2 at the end:
=A2 * IF(B2 <= -100, 1 - 100/B2, IF(B2 >= 100, 1 + B2/100))-A2
That seemed to work.
So how would I add the W/L column? Let's say I make that E2.
=if(e2="w", .... the rest?

 

[shg]

I don't understand the result you're looking for. Running total win/loss?

 

[shg]

This?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Bet​

[/td][td="bgcolor:#F3F3F3"]

Moneyline​

[/td][td="bgcolor:#F3F3F3"]

W/L​

[/td][td="bgcolor:#F3F3F3"]

Result​

[/td][td="bgcolor:#F3F3F3"]

Net​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

$100.00​

[/td][td]

200​

[/td][td]

W​

[/td][td="bgcolor:#CCFFCC"]

$200.00​

[/td][td="bgcolor:#CCECFF"]

$200.00​

[/td][td="bgcolor:#CCFFCC"]D2: =IF(C2 = "W", A2 * IF(B2 <= -100, -100/B2, IF(B2 >= 100, B2/100)), -A2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

$100.00​

[/td][td]

-210​

[/td][td]

W​

[/td][td="bgcolor:#CCFFCC"]

$47.62​

[/td][td="bgcolor:#CCECFF"]

$247.62​

[/td][td="bgcolor:#CCECFF"]E2: =SUM(E1, D2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

$100.00​

[/td][td]

-150​

[/td][td]

L​

[/td][td="bgcolor:#CCFFCC"]

($100.00)​

[/td][td="bgcolor:#CCECFF"]

$147.62​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

$100.00​

[/td][td]

290​

[/td][td]

L​

[/td][td="bgcolor:#CCFFCC"]

($100.00)​

[/td][td="bgcolor:#CCECFF"]

$47.62​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

$100.00​

[/td][td]

240​

[/td][td]

L​

[/td][td="bgcolor:#CCFFCC"]

($100.00)​

[/td][td="bgcolor:#CCECFF"]

($52.38)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

$100.00​

[/td][td]

-290​

[/td][td]

L​

[/td][td="bgcolor:#CCFFCC"]

($100.00)​

[/td][td="bgcolor:#CCECFF"]

($152.38)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

$100.00​

[/td][td]

140​

[/td][td]

L​

[/td][td="bgcolor:#CCFFCC"]

($100.00)​

[/td][td="bgcolor:#CCECFF"]

($252.38)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

$100.00​

[/td][td]

270​

[/td][td]

W​

[/td][td="bgcolor:#CCFFCC"]

$270.00​

[/td][td="bgcolor:#CCECFF"]

$17.62​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

$100.00​

[/td][td]

-150​

[/td][td]

L​

[/td][td="bgcolor:#CCFFCC"]

($100.00)​

[/td][td="bgcolor:#CCECFF"]

($82.38)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

$100.00​

[/td][td]

120​

[/td][td]

L​

[/td][td="bgcolor:#CCFFCC"]

($100.00)​

[/td][td="bgcolor:#CCECFF"]

($182.38)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

$100.00​

[/td][td]

-190​

[/td][td]

L​

[/td][td="bgcolor:#CCFFCC"]

($100.00)​

[/td][td="bgcolor:#CCECFF"]

($282.38)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

$100.00​

[/td][td]

110​

[/td][td]

W​

[/td][td="bgcolor:#CCFFCC"]

$110.00​

[/td][td="bgcolor:#CCECFF"]

($172.38)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

$100.00​

[/td][td]

-130​

[/td][td]

W​

[/td][td="bgcolor:#CCFFCC"]

$76.92​

[/td][td="bgcolor:#CCECFF"]

($95.46)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

$100.00​

[/td][td]

110​

[/td][td]

W​

[/td][td="bgcolor:#CCFFCC"]

$110.00​

[/td][td="bgcolor:#CCECFF"]

$14.54​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

$100.00​

[/td][td]

-240​

[/td][td]

L​

[/td][td="bgcolor:#CCFFCC"]

($100.00)​

[/td][td="bgcolor:#CCECFF"]

($85.46)​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

$100.00​

[/td][td]

-260​

[/td][td]

W​

[/td][td="bgcolor:#CCFFCC"]

$38.46​

[/td][td="bgcolor:#CCECFF"]

($47.00)​

[/td][td][/td][/tr]
[/table]