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]

I'm currently using

=SUMPRODUCT((ISNUMBER(SEARCH("!",$B4:$B53)))*($C4:$C53>=$D4:$D53)*(G4:G53>0))
+
SUMPRODUCT((ISNUMBER(SEARCH("!",$A4:$A53)))*($D4:$D53>=$C4:$C53)*(G4:G53<0))

which I just put in my sample sheet titled "with SUMPRODUCT"
So I wonder if with SUMPRODUCT you can modify the formula to use the new earnings columns to calculate my new score formula. I couldn't make it work. Something tells me SUMPRODUCT wouldn't be the right function.

 

[juntjoo]

still not following , sorry , i dont understand odds/betting, others may, so for me all i need is the logic based on the columns E & F
How you arrive at the answer in J
then we can use that to get excel to calculate inside of you
so , whats the math
Lets take ROW 5 - how do you get to -100 , based on the figures entered in the other columns on the same rows
and any different permutation that would be needed to achieve each of those row results row 5 to row 9

-100
50
20
83
-100

in terms of those cells rather than words with maths / arithmetic

thanks. Hang tight. Gonna reply in just a moment...

 

[juntjoo]

still not following , sorry , i dont understand odds/betting, others may, so for me all i need is the logic based on the columns E & F
How you arrive at the answer in J
then we can use that to get excel to calculate inside of you
so , whats the math
Lets take ROW 5 - how do you get to -100 , based on the figures entered in the other columns on the same rows
and any different permutation that would be needed to achieve each of those row results row 5 to row 9

-100
50
20
83
-100

in terms of those cells rather than words with maths / arithmetic

"so for me all i need is the logic based on the columns E & F"

check post #5

"How you arrive at the answer in J "

As stated in post #6 "Columns c, d, e & F show how odds work, and column J are notes as to how I use odds calculations to come up with my scoring formula (minus the initial base $100 bet)

 

[juntjoo]

still not following , sorry , i dont understand odds/betting, others may, so for me all i need is the logic based on the columns E & F
How you arrive at the answer in J
then we can use that to get excel to calculate inside of you
so , whats the math
Lets take ROW 5 - how do you get to -100 , based on the figures entered in the other columns on the same rows
and any different permutation that would be needed to achieve each of those row results row 5 to row 9

-100
50
20
83
-100

in terms of those cells rather than words with maths / arithmetic

once sec.. I think I confused things...

 

[juntjoo]

still not following , sorry , i dont understand odds/betting, others may, so for me all i need is the logic based on the columns E & F
How you arrive at the answer in J
then we can use that to get excel to calculate inside of you
so , whats the math
Lets take ROW 5 - how do you get to -100 , based on the figures entered in the other columns on the same rows
and any different permutation that would be needed to achieve each of those row results row 5 to row 9

-100
50
20
83
-100

in terms of those cells rather than words with maths / arithmetic

okay, in post #6 where I say:

"Columns c, d, e & F show how odds work, and column J are notes as to how I use odds calculations to come up with my scoring formula (minus the initial base $100 bet)"

Take out the "(minus the intial base $100 bet)" part. Indeed the $100 base bet is reflected in the "-100" scores. I don't know what I was thinking when I said that

So yeah, I want my scores based on the base $100 bet where you either lose 100 points or gain whatever based on the odds. How's this sound now lol? Sorry, like I said it's complicated but will come up with nice scoring system for bettors I follow.

Before as now the file shows in the additional sheet I added with my current formula, it's only based binarilly(?) on whether they won or didn't. This way it's based on the accumulative odds of their wins and losses

 

[etaf]

still not following really sorry trying my best too
re-read all the posts again

Assuming your happy with the IF formulas in G & H
for each row - can you show how you arrive at the numbers in J ?

lets take
G2 unsuccessful picks

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

how do you even show a loss ? J5 & J9 are showing -100 - so a loss - why ? how do i know its a loss
even the ! is in different parts of a cell

See column L

bettor score (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
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
5	Mayra Cantuaria!	Jamey Lyn Horth Wessels	-125	-105	$80	$95	-1		=	-100		how do you get -100
6	!Tony Laramie	Tyus White	-200	160	$50	$160	1		=	50		how do you get 50
7	Royce White	Daiqkwon Buckley !	-150	120	$67	$120	-1		=	20		how do you get 20
8	Jalin Fuller!	Bruno Assis	-120	-110	$83	$91	1		=	83		how do you get 83
9	!Felipe Martinez Donis	Henry Huff	-225	185	$44	$185	-1		=	-100		how do you get -100
10							*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
weekly betting analysis

Cell Formulas
Range	Formula
E5:E9	E5	=IF([@odds]<0,-10000/[@odds],[@odds])
F5:F9	F5	=IF([@odds2]<0,-10000/[@odds2],[@odds2])
H14	H14	=COUNTIF(G5:G9, -1)*-100

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:A9	Cell Value	contains "bellator"	text	NO
A4:A9	Cell Value	contains "LFA"	text	NO
A4:A9	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

 

[juntjoo]

still not following really sorry trying my best too
re-read all the posts again

Assuming your happy with the IF formulas in G & H
for each row - can you show how you arrive at the numbers in J ?

lets take
G2 unsuccessful picks

how do you even show a loss ? J5 & J9 are showing -100 - so a loss - why ? how do i know its a loss
even the ! is in different parts of a cell

See column L

bettor score (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
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
5	Mayra Cantuaria!	Jamey Lyn Horth Wessels	-125	-105	$80	$95	-1		=	-100		how do you get -100
6	!Tony Laramie	Tyus White	-200	160	$50	$160	1		=	50		how do you get 50
7	Royce White	Daiqkwon Buckley !	-150	120	$67	$120	-1		=	20		how do you get 20
8	Jalin Fuller!	Bruno Assis	-120	-110	$83	$91	1		=	83		how do you get 83
9	!Felipe Martinez Donis	Henry Huff	-225	185	$44	$185	-1		=	-100		how do you get -100
10							*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
weekly betting analysis

Cell Formulas
Range	Formula
E5:E9	E5	=IF([@odds]<0,-10000/[@odds],[@odds])
F5:F9	F5	=IF([@odds2]<0,-10000/[@odds2],[@odds2])
H14	H14	=COUNTIF(G5:G9, -1)*-100

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:A9	Cell Value	contains "bellator"	text	NO
A4:A9	Cell Value	contains "LFA"	text	NO
A4:A9	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

No prob. Thanks so much again for your time.

So columns E & F are the calculations for what you win according to the odds based on a $100 bet, as odds are figured that way, off $100 bet.

Column J, my score column also based off a base $100 bet reflects the bettor's score based on his bets in column G, which will ultimately have to be based off the calculations of E & F.

1st fight, the bettor straight lost his $100, = -100 points. No calculations needed but just checking who won(! in column A) and who the bettor bet on(1 or -1 in column G)

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.

Oh, the ! exclamation mark can be anywhere in the cell. Just that it's in there indicates the winner.

Hope that clears it up.

 

[etaf]

ok,
lets stay with this then

1st fight, the bettor straight lost his $100, = -100 points. No calculations needed but just checking who won(! in column A) and who the bettor bet on(1 or -1 in column G)

we can search a cell for existence of a !

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

we can find a winner or loser then
so
=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),"",-100)
will find the loser
and put -100 in the cell - i'm using column O as a helper , like your J

once sorted i can move onto the win part

is that correctly worked out for all possible losers , if so then we can sort a formula out for G2

bettor score (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
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
6	!Tony Laramie	Tyus White	-200	160	$50	$160	1		=	50		how do you get 50
7	Royce White	Daiqkwon Buckley !	-150	120	$67	$120	-1		=	20		how do you get 20
8	Jalin Fuller!	Bruno Assis	-120	-110	$83	$91	1		=	83		how do you get 83
9	!Felipe Martinez Donis	Henry Huff	-225	185	$44	$185	-1		=	-100		how do you get -100			-100
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
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)),"",-100)
H14	H14	=COUNTIF(G5:G9, -1)*-100

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

 

[etaf]

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 ?

 

[juntjoo]

ok,
lets stay with this then

we can search a cell for existence of a !


we can find a winner or loser then
so
=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),"",-100)
will find the loser
and put -100 in the cell - i'm using column O as a helper , like your J

once sorted i can move onto the win part

is that correctly worked out for all possible losers , if so then we can sort a formula out for G2

bettor score (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
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
6	!Tony Laramie	Tyus White	-200	160	$50	$160	1		=	50		how do you get 50
7	Royce White	Daiqkwon Buckley !	-150	120	$67	$120	-1		=	20		how do you get 20
8	Jalin Fuller!	Bruno Assis	-120	-110	$83	$91	1		=	83		how do you get 83
9	!Felipe Martinez Donis	Henry Huff	-225	185	$44	$185	-1		=	-100		how do you get -100			-100
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
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)),"",-100)
H14	H14	=COUNTIF(G5:G9, -1)*-100

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

Wait, when you say "helper", do you mean as in a whole other column? My J column is only to help you see the formulation needed ultimately inside of G1 & 2.

With "helper" columns I could figure this out. Not my goal here. I've got several "bettors" and don't want to have more than one column for each one.

Or did I misunderstand you?

Check the file again, I added another sheet using my current formula with "SUMPRODUCT" I have been using with a more simple scoring calculation. I was hoping to use it for my new idea, and tried to insert in there but appears it's not possible within the parameters of the function. Maybe you can do something with SUMPRODUCT or similar.

But NO extra columns. Trying to keep this neat. Thanks!