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?

	A​	B​	C​	D​	E​
1​	Bet​	Moneyline​	Pays​	Profit​	​
2​	$ 100.00​	-200​	$ 150.00​	$ 50.00​	C2: =A2 * IF(B2 <= -100, 1 - 100/B2, IF(B2 >= 100, 1 + B2/100))
3​	$ 100.00​	-180​	$ 155.56​	$ 55.56​	D2: =C2 - A2
4​	$ 100.00​	-160​	$ 162.50​	$ 62.50​
5​	$ 100.00​	-140​	$ 171.43​	$ 71.43​
6​	$ 100.00​	-120​	$ 183.33​	$ 83.33​
7​	$ 100.00​	-100​	$ 200.00​	$ 100.00​
8​	$ 100.00​	100​	$ 200.00​	$ 100.00​
9​	$ 100.00​	120​	$ 220.00​	$ 120.00​
10​	$ 100.00​	140​	$ 240.00​	$ 140.00​
11​	$ 100.00​	160​	$ 260.00​	$ 160.00​
12​	$ 100.00​	180​	$ 280.00​	$ 180.00​
13​	$ 100.00​	200​	$ 300.00​	$ 200.00​

 

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

	A​	B​	C​	D​	E​	F​
1​	Bet​	Moneyline​	W/L​	Result​	Net​	​
2​	$100.00​	200​	W​	$200.00​	$200.00​	D2: =IF(C2 = "W", A2 * IF(B2 <= -100, -100/B2, IF(B2 >= 100, B2/100)), -A2)
3​	$100.00​	-210​	W​	$47.62​	$247.62​	E2: =SUM(E1, D2)
4​	$100.00​	-150​	L​	($100.00)​	$147.62​
5​	$100.00​	290​	L​	($100.00)​	$47.62​
6​	$100.00​	240​	L​	($100.00)​	($52.38)​
7​	$100.00​	-290​	L​	($100.00)​	($152.38)​
8​	$100.00​	140​	L​	($100.00)​	($252.38)​
9​	$100.00​	270​	W​	$270.00​	$17.62​
10​	$100.00​	-150​	L​	($100.00)​	($82.38)​
11​	$100.00​	120​	L​	($100.00)​	($182.38)​
12​	$100.00​	-190​	L​	($100.00)​	($282.38)​
13​	$100.00​	110​	W​	$110.00​	($172.38)​
14​	$100.00​	-130​	W​	$76.92​	($95.46)​
15​	$100.00​	110​	W​	$110.00​	$14.54​
16​	$100.00​	-240​	L​	($100.00)​	($85.46)​
17​	$100.00​	-260​	W​	$38.46​	($47.00)​