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

 

[etaf]

EDIT
I see its also crossposted here

Sign in to your account

techcommunity.microsoft.com

Which i have not looked at (Microsoft needs all sorts of registry, and hoops to login, and not doing all that just to look at your crosspost) - but as you have posted , not sure if this is solved, so i wont spend to much time on this , if its already resolved elsewhere

i dont know how you got to the results , a little difficult to follow
Can you explain why you have
153 in G1
and also why you have
200 in G2

I thought it was a striaght forward =SUMIF(J5:J9,">1") and in the other forum
you said

The J column was just to show how I want the score calculated in G1 and G2.

I dont understand the notes and it would be better if you told us why and what logic you are using, that way a formula could be derived

As you say J column are the row results - can you explain how they are derived?

For others I have posted the spreadsheet here using XL2BB
See my NOTE with SUMIF() using J column - Labelled WAYNE

bettor score.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
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
6	!Tony Laramie	Tyus White	-200	160	$50	$160	1		=	50
7	Royce White	Daiqkwon Buckley !	-150	120	$67	$120	-1		=	20
8	Jalin Fuller!	Bruno Assis	-120	-110	$83	$91	1		=	83
9	!Felipe Martinez Donis	Henry Huff	-225	185	$44	$185	-1		=	-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									Wayne - SUMIF() - But need to know how J column is derived
14									Succesful	153
15									Unsuccessful	200
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])
J14	J14	=SUMIF(J5:J9,">1")
J15	J15	=(SUMIF(J5:J9,"<1"))*-1

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]

EDIT
I see its also crossposted here

Sign in to your account

techcommunity.microsoft.com

Which i have not looked at (Microsoft needs all sorts of registry, and hoops to login, and not doing all that just to look at your crosspost) - but as you have posted , not sure if this is solved, so i wont spend to much time on this , if its already resolved elsewhere

i dont know how you got to the results , a little difficult to follow
Can you explain why you have
153 in G1
and also why you have
200 in G2

I thought it was a striaght forward =SUMIF(J5:J9,">1") and in the other forum
you said


I dont understand the notes and it would be better if you told us why and what logic you are using, that way a formula could be derived

As you say J column are the row results - can you explain how they are derived?

For others I have posted the spreadsheet here using XL2BB
See my NOTE with SUMIF() using J column - Labelled WAYNE

bettor score.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
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
6	!Tony Laramie	Tyus White	-200	160	$50	$160	1		=	50
7	Royce White	Daiqkwon Buckley !	-150	120	$67	$120	-1		=	20
8	Jalin Fuller!	Bruno Assis	-120	-110	$83	$91	1		=	83
9	!Felipe Martinez Donis	Henry Huff	-225	185	$44	$185	-1		=	-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									Wayne - SUMIF() - But need to know how J column is derived
14									Succesful	153
15									Unsuccessful	200
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])
J14	J14	=SUMIF(J5:J9,">1")
J15	J15	=(SUMIF(J5:J9,"<1"))*-1

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

Thanks.
E and F columns show how I calculate score earnings on a bet based on odds. Then I show how I want that translated into the score in the note on the right, which is minus the initial base $100 bet. Hence the two losses would be - 100 and the wins would be the earnings minus 100. Then the ultimate goal is to get all that under G1 and 2. Then I can add columns for additional bettors to compare ongoing scores. Make sense? I'm trying to get formulas for g1&2

 

[juntjoo]

The

EDIT
I see its also crossposted here

Sign in to your account

techcommunity.microsoft.com

Which i have not looked at (Microsoft needs all sorts of registry, and hoops to login, and not doing all that just to look at your crosspost) - but as you have posted , not sure if this is solved, so i wont spend to much time on this , if its already resolved elsewhere

i dont know how you got to the results , a little difficult to follow
Can you explain why you have
153 in G1
and also why you have
200 in G2

I thought it was a striaght forward =SUMIF(J5:J9,">1") and in the other forum
you said


I dont understand the notes and it would be better if you told us why and what logic you are using, that way a formula could be derived

As you say J column are the row results - can you explain how they are derived?

For others I have posted the spreadsheet here using XL2BB
See my NOTE with SUMIF() using J column - Labelled WAYNE

bettor score.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
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
6	!Tony Laramie	Tyus White	-200	160	$50	$160	1		=	50
7	Royce White	Daiqkwon Buckley !	-150	120	$67	$120	-1		=	20
8	Jalin Fuller!	Bruno Assis	-120	-110	$83	$91	1		=	83
9	!Felipe Martinez Donis	Henry Huff	-225	185	$44	$185	-1		=	-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									Wayne - SUMIF() - But need to know how J column is derived
14									Succesful	153
15									Unsuccessful	200
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])
J14	J14	=SUMIF(J5:J9,">1")
J15	J15	=(SUMIF(J5:J9,"<1"))*-1

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

The 153 and 200 results are the total of the individual score calculations I show in the notes on the right that somehow I want to be integrated into g1&2. The odds formulas can be linked to from columns e & f. But again, minus the initial $100. Odds are determined by $100

 

[juntjoo]

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

 

[juntjoo]

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)

 

[Fluff]

If you have posted the question anywhere other than ExcelForum, please supply links to those site(s)

 

[juntjoo]

If you have posted the question anywhere other than ExcelForum, please supply links to those site(s)

Sorry.
help with ongoing bettor score please

Is there supposed to be an edit button to my original post? Don't see one. I'm currently on my phone though in "desktop" mode

 

[etaf]

No the edit is only visible for 10mins
I’m out now for a while so won’t be able to look at your explanation but looking in the phone makes a bit more sense now I hope

 

[etaf]

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