Need help with complicated formula for keeping a score

[juntjoo]

So this is my bettor score table again. I hope I made it as self-explanatory as possible. I put notes in there. The end calculations I'm looking for help with are the total score in the top pink and green cells over the a bettor's ongoing game/fight score column.

There's a couple factors involved, the victor of the fight and the bettor's pick. Much thanks in advance as this to me looks really complicated, if anyone has the time to try this out for me.

I've cross-posted this here:

excel forum

here's the file:

sample sheet

 

[juntjoo]

Winners
Row E & F
E6 is 50 , and because theres a 1 in G6 , then my O6 should read that 50 from E6 , if it was a -1 then it would read F6
so that works for row 6

Row7
now G7 is a -1 , so results in F7
So why isnt F7 20 - as the 100 should be taken off , like the G8
then my O7 should read that ??? 20 , but its 120 from F7 , if it was a 1 then it would read E7, in this case the 100 has not been removed ?

Row8
G8 is a 1 , so results from E8 , which is 83 then my O8 should read that 83 from E8 , if it was a -1 then it would read F8

So why is row 7 different ?

oh you make a good point... maybe... One sec...

 

[juntjoo]

Winners
Row E & F
E6 is 50 , and because theres a 1 in G6 , then my O6 should read that 50 from E6 , if it was a -1 then it would read F6
so that works for row 6

Row7
now G7 is a -1 , so results in F7
So why isnt F7 20 - as the 100 should be taken off , like the G8
then my O7 should read that ??? 20 , but its 120 from F7 , if it was a 1 then it would read E7, in this case the 100 has not been removed ?

Row8
G8 is a 1 , so results from E8 , which is 83 then my O8 should read that 83 from E8 , if it was a -1 then it would read F8

So why is row 7 different ?

Got it! God I hope so. Numbers really screw with my head. I know how this betting "odds" stuff works but not numbers as I presume you do better than me. So long as you got this odds thing, and the logic, or ill logic of what I'm trying to do here, then we're getting somewhere.

I was right but a little off. Please check the file again. I just updated it. It should make sense now. Again, no "helper" columns in the final product if possible.

 

[etaf]

opened the file , cant see the reason for the 120 in row7
can you explain why row 7 has 120 in and not 20 ? F7
BUT G6 & G8 has the correct amounts less the 100 stake

so you dont want column J with a calc - which maybe the best way - or even on a separate sheet
if we get it working correctly with a helper column then we can look at doing it without

 

[juntjoo]

opened the file , cant see the reason for the 120 in row7
can you explain why row 7 has 120 in and not 20 ? F7
BUT G6 & G8 has the correct amounts less the 100 stake

so you dont want column J with a calc - which maybe the best way - or even on a separate sheet
if we get it working correctly with a helper column then we can look at doing it without

K, so column J is exactly what you walk away with from our little "casino" per bet, to simplify. Treat each one individually to see what's going on

Row 7 has a successful bet on an underdog winning the bettor $120 ON TOP of their $100 fee to play. So they go home with an EXTRA $120, or 120 points.

whereas in...

Row 8 we again have a successful bet but on a favorite, so we only make $83 on our initial $100 fee to play so we go home with an EXTRA $83, or 83 points.

and in...

Rows 9 & 5 our bettor simply loses losing their $100 fee to play, losing 100 points(-100)

Am I right? I could be wrong I acknowledge as my numbers are often off. I think I got it right though this time.

 

[juntjoo]

opened the file , cant see the reason for the 120 in row7
can you explain why row 7 has 120 in and not 20 ? F7
BUT G6 & G8 has the correct amounts less the 100 stake

so you dont want column J with a calc - which maybe the best way - or even on a separate sheet
if we get it working correctly with a helper column then we can look at doing it without

yeah my initial idea of "minus the $100" stake was a mis-communication or thinking. We gotta factor in this "fee to play" as when bettor loses, he loses it, so that has to be factored in. And I believe I straightened that out with my update.

 

[etaf]

so why do you have 20 in J7
and not 120 ?

 

[juntjoo]

so why do you have 20 in J7
and not 120 ?

has it not been updated? Thought I did that way back... Maybe dropbox didn't get it...

 

[juntjoo]

so why do you have 20 in J7
and not 120 ?

should be up to date

 

[etaf]

OK, so then if its a WIN
depending on if a 1 or -1 we just copy across E or F
so now why is row8 83 , a fraction and is that what you want round down or up ?

=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)

ignore for the moment you dont want help colums
does the calculations now replace column J?

so can column J stay - then its all easy using the formulas for G1 and G2

bettor score (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	successful picks				*based off		153			*calculations to produce score per bet/pick based on a $100 bet
2	unsuccessful picks				$100 bet		200			* losses are straight -$100 loss, where as wins require formula
3	fighter 1	fighter2	odds	odds2	earnings	earnings2	bettor 1			*I would prefer these points be calculated within G1 and G2 total ongoing score cells, not in their own column.
4	LFA 120									individual fight scores					Wayne Help
5	Mayra Cantuaria!	Jamey Lyn Horth Wessels	-125	-105	$80	$95	-1		=	-100		how do you get -100			-100	95.2381
6	!Tony Laramie	Tyus White	-200	160	$50	$160	1		=	50		how do you get 50			50	50
7	Royce White	Daiqkwon Buckley !	-150	120	$67	$120	-1		=	20		how do you get 20			120	120
8	Jalin Fuller!	Bruno Assis	-120	-110	$83	$91	1		=	83		how do you get 83			83.33333	83.33333
9	!Felipe Martinez Donis	Henry Huff	-225	185	$44	$185	-1		=	-100		how do you get -100			-100	185
10					$0	$0	*a "1" is a pick on the first fighter, a "-1" for the second fighter
11	*the victor has a "!" in their cell
12
13	In simple terms a fighter's/team's/horse's odds of say +100 would mean on a $100 bet you'd win $100, whereas odds of -130 you'd have to bet $130 to win $100, or odds of +250 you'd win $250 off a $100 bet
14								-300
15
16
17	2nd fight, bettor correctly picked the favorite, favorite with negative odds of -200, meaning you gotta bet $200 to earn $100, so with my base $100 bet scoring system, he earns 50 points($50 of a $100 bet) 3rd fight, bettor picked correct again, taking the underdog with positive odds of +120, a potential of $120 on a $100 bet, earning 20 points on my system.									0.5
18										0.833333333
19
20
21
22
weekly betting analysis

Cell Formulas
Range	Formula
E5:E10	E5	=IF([@odds]<0,-10000/[@odds],[@odds])
F5:F10	F5	=IF([@odds2]<0,-10000/[@odds2],[@odds2])
O5:O9	O5	=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)
P5:P9	P5	=IF(G5=-1,F5,E5)
H14	H14	=COUNTIF(G5:G9, -1)*-100
J17	J17	=100/200
J18	J18	=100/120

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:A10	Cell Value	contains "bellator"	text	NO
A4:A10	Cell Value	contains "LFA"	text	NO
A4:A10	Cell Value	contains "UFC"	text	NO
C5:D9	Cell Value	<0	text	NO
C5:D9	Cell Value	>0	text	NO
A4,A5:B9	Cell Value	contains "!"	text	NO

successful picks

*based off

153​

*calculations to produce score per bet/pick based on a $100 bet

unsuccessful picks

$100 bet

200​

* losses are straight -$100 loss, where as wins require formula

fighter 1

fighter2

odds

odds2

earnings

earnings2

bettor 1

*I would prefer these points be calculated within G1 and G2 total ongoing score cells, not in their own column.

LFA 120

individual fight scores

Wayne Help

Mayra Cantuaria!

Jamey Lyn Horth Wessels

-125

-105

$80​

$95​

-1

=

-100​

how do you get -100

-100

95.2381​

!Tony Laramie

Tyus White

-200

160

$50​

$160​

1

=

50​

how do you get 50

50

50​

Royce White

Daiqkwon Buckley !

-150

120

$67​

$120​

-1

=

20​

how do you get 20

120

120​

Jalin Fuller!

Bruno Assis

-120

-110

$83​

$91​

1

=

83​

how do you get 83

83.33333

83.33333​

!Felipe Martinez Donis

Henry Huff

-225

185

$44​

$185​

-1

=

-100​

how do you get -100

-100

185​

$0

$0

*a "1" is a pick on the first fighter, a "-1" for the second fighter

*the victor has a "!" in their cell

In simple terms a fighter's/team's/horse's odds of say +100 would mean on a $100 bet you'd win $100, whereas odds of -130 you'd have to bet $130 to win $100, or odds of +250 you'd win $250 off a $100 bet

-300​

2nd fight, bettor correctly picked the favorite, favorite with negative odds of -200, meaning you gotta bet $200 to earn $100, so with my base $100 bet scoring system, he earns 50 points($50 of a $100 bet) 3rd fight, bettor picked correct again, taking the underdog with positive odds of +120, a potential of $120 on a $100 bet, earning 20 points on my system.

0.5​

0.833333333​

 

[juntjoo]

OK, so then if its a WIN
depending on if a 1 or -1 we just copy across E or F
so now why is row8 83 , a fraction and is that what you want round down or up ?

=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)

ignore for the moment you dont want help colums
does the calculations now replace column J?

so can column J stay - then its all easy using the formulas for G1 and G2

bettor score (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	successful picks				*based off		153			*calculations to produce score per bet/pick based on a $100 bet
2	unsuccessful picks				$100 bet		200			* losses are straight -$100 loss, where as wins require formula
3	fighter 1	fighter2	odds	odds2	earnings	earnings2	bettor 1			*I would prefer these points be calculated within G1 and G2 total ongoing score cells, not in their own column.
4	LFA 120									individual fight scores					Wayne Help
5	Mayra Cantuaria!	Jamey Lyn Horth Wessels	-125	-105	$80	$95	-1		=	-100		how do you get -100			-100	95.2381
6	!Tony Laramie	Tyus White	-200	160	$50	$160	1		=	50		how do you get 50			50	50
7	Royce White	Daiqkwon Buckley !	-150	120	$67	$120	-1		=	20		how do you get 20			120	120
8	Jalin Fuller!	Bruno Assis	-120	-110	$83	$91	1		=	83		how do you get 83			83.33333	83.33333
9	!Felipe Martinez Donis	Henry Huff	-225	185	$44	$185	-1		=	-100		how do you get -100			-100	185
10					$0	$0	*a "1" is a pick on the first fighter, a "-1" for the second fighter
11	*the victor has a "!" in their cell
12
13	In simple terms a fighter's/team's/horse's odds of say +100 would mean on a $100 bet you'd win $100, whereas odds of -130 you'd have to bet $130 to win $100, or odds of +250 you'd win $250 off a $100 bet
14								-300
15
16
17	2nd fight, bettor correctly picked the favorite, favorite with negative odds of -200, meaning you gotta bet $200 to earn $100, so with my base $100 bet scoring system, he earns 50 points($50 of a $100 bet) 3rd fight, bettor picked correct again, taking the underdog with positive odds of +120, a potential of $120 on a $100 bet, earning 20 points on my system.									0.5
18										0.833333333
19
20
21
22
weekly betting analysis

Cell Formulas
Range	Formula
E5:E10	E5	=IF([@odds]<0,-10000/[@odds],[@odds])
F5:F10	F5	=IF([@odds2]<0,-10000/[@odds2],[@odds2])
O5:O9	O5	=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)
P5:P9	P5	=IF(G5=-1,F5,E5)
H14	H14	=COUNTIF(G5:G9, -1)*-100
J17	J17	=100/200
J18	J18	=100/120

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:A10	Cell Value	contains "bellator"	text	NO
A4:A10	Cell Value	contains "LFA"	text	NO
A4:A10	Cell Value	contains "UFC"	text	NO
C5:D9	Cell Value	<0	text	NO
C5:D9	Cell Value	>0	text	NO
A4,A5:B9	Cell Value	contains "!"	text	NO

successful picks

*based off

153​

*calculations to produce score per bet/pick based on a $100 bet

unsuccessful picks

$100 bet

200​

* losses are straight -$100 loss, where as wins require formula

fighter 1

fighter2

odds

odds2

earnings

earnings2

bettor 1

*I would prefer these points be calculated within G1 and G2 total ongoing score cells, not in their own column.

LFA 120

individual fight scores

Wayne Help

Mayra Cantuaria!

Jamey Lyn Horth Wessels

-125

-105

$80​

$95​

-1

=

-100​

how do you get -100

-100

95.2381​

!Tony Laramie

Tyus White

-200

160

$50​

$160​

1

=

50​

how do you get 50

50

50​

Royce White

Daiqkwon Buckley !

-150

120

$67​

$120​

-1

=

20​

how do you get 20

120

120​

Jalin Fuller!

Bruno Assis

-120

-110

$83​

$91​

1

=

83​

how do you get 83

83.33333

83.33333​

!Felipe Martinez Donis

Henry Huff

-225

185

$44​

$185​

-1

=

-100​

how do you get -100

-100

185​

$0

$0

*a "1" is a pick on the first fighter, a "-1" for the second fighter

*the victor has a "!" in their cell

In simple terms a fighter's/team's/horse's odds of say +100 would mean on a $100 bet you'd win $100, whereas odds of -130 you'd have to bet $130 to win $100, or odds of +250 you'd win $250 off a $100 bet

-300​

2nd fight, bettor correctly picked the favorite, favorite with negative odds of -200, meaning you gotta bet $200 to earn $100, so with my base $100 bet scoring system, he earns 50 points($50 of a $100 bet) 3rd fight, bettor picked correct again, taking the underdog with positive odds of +120, a potential of $120 on a $100 bet, earning 20 points on my system.

0.5​

0.833333333​

thinking hard...