Need help with complicated formula for keeping a score

J

juntjoo

Board Regular
Joined
Dec 8, 2009
Messages
53
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
 
Sort by date Sort by votes
etaf said:
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
ABCDEFGHIJKLMNOPQ
1successful picks*based off 153*calculations to produce score per bet/pick based on a $100 bet
2unsuccessful picks$100 bet200* losses are straight -$100 loss, where as wins require formula
3fighter 1fighter2oddsodds2earningsearnings2bettor 1*I would prefer these points be calculated within G1 and G2 total ongoing score cells, not in their own column.
4LFA 120individual fight scoresWayne Help
5Mayra Cantuaria!Jamey Lyn Horth Wessels-125-105$80$95-1=-100how do you get -100-10095.2381
6!Tony LaramieTyus White-200160$50$1601=50how do you get 505050
7Royce WhiteDaiqkwon Buckley !-150120$67$120-1=20how do you get 20120120
8Jalin Fuller!Bruno Assis-120-110$83$911=83how do you get 8383.3333383.33333
9!Felipe Martinez DonisHenry Huff-225185$44$185-1=-100how do you get -100-100185
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
13In 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
172nd 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
180.833333333
19
20
21
22
weekly betting analysis
Cell Formulas
RangeFormula
E5:E10E5=IF([@odds]<0,-10000/[@odds],[@odds])
F5:F10F5=IF([@odds2]<0,-10000/[@odds2],[@odds2])
O5:O9O5=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)
P5:P9P5=IF(G5=-1,F5,E5)
H14H14=COUNTIF(G5:G9, -1)*-100
J17J17=100/200
J18J18=100/120
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:A10Cell Valuecontains "bellator"textNO
A4:A10Cell Valuecontains "LFA"textNO
A4:A10Cell Valuecontains "UFC"textNO
C5:D9Cell Value<0textNO
C5:D9Cell Value>0textNO
A4,A5:B9Cell Valuecontains "!"textNO



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 1fighter2oddsodds2earningsearnings2bettor 1*I would prefer these points be calculated within G1 and G2 total ongoing score cells, not in their own column.
LFA 120individual fight scoresWayne Help
Mayra Cantuaria!Jamey Lyn Horth Wessels-125-105
$80​
$95​
-1=
-100​
how do you get -100-100
95.2381​
!Tony LaramieTyus White-200160
$50​
$160​
1=
50​
how do you get 5050
50​
Royce WhiteDaiqkwon Buckley !-150120
$67​
$120​
-1=
20​
how do you get 20120
120​
Jalin Fuller!Bruno Assis-120-110
$83​
$91​
1=
83​
how do you get 8383.33333
83.33333​
!Felipe Martinez DonisHenry Huff-225185
$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​
Click to expand...
"so now why is row8 83 , a fraction and is that what you want round down or up ?"

According to the odds which in this case are negative, negative for a "favorite", your $100 wins you a part of it in addition.

If the odds were positive you'd win all your $100 back plus more. It's always a fraction, unless it's a +100 odds.

No "rounding". That's why I'm doing this as opposed to the binary method I had been using of either win or lose. I want each win to reflect the odds of the win. If bettor just wins

I just moved my G total calcs over J just to show what they reflect vs column K, which I just put out there to consider, but no, J is the system I want to go by, I believe will create a logical accurate scoring system for a bettor's abilities as a prophet to profit. I say this with less 100% surety. But I'm pretty sure this will be good.
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
if you dont need rounding , and keeping J - then its done
in
J5 put
=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)
and copy down

In

G1 = Succesful Picks
=SUMIF(J5:J9,">1")

G2 = Unsuccesful Picks
=(SUMIF(J5:J9,"<1"))*-1

then you have the solution to the original question , how to get G1 & G2 , with J calculated by excel

bettor score (1).xlsx
ABCDEFGHIJK
1successful picks*based off 253*calculations to produce score per bet/pick based on a $100 bet
2unsuccessful picks$100 bet200* losses are straight -$100 loss, where as wins require formula
3fighter 1fighter2oddsodds2earningsearnings2bettor 1*I would prefer these points be calculated within G1 and G2 total ongoing score cells, not in their own column.
4LFA 120individual fight scores
5Mayra Cantuaria!Jamey Lyn Horth Wessels-125-105$80$95-1=-100
6!Tony LaramieTyus White-200160$50$1601=50
7Royce WhiteDaiqkwon Buckley !-150120$67$120-1=120
8Jalin Fuller!Bruno Assis-120-110$83$911=83.33333333
9!Felipe Martinez DonisHenry Huff-225185$44$185-1=-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
weekly betting analysis
Cell Formulas
RangeFormula
G1G1=SUMIF(J5:J9,">1")
G2G2=(SUMIF(J5:J9,"<1"))*-1
E5:E10E5=IF([@odds]<0,-10000/[@odds],[@odds])
F5:F10F5=IF([@odds2]<0,-10000/[@odds2],[@odds2])
J5:J9J5=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:A10Cell Valuecontains "bellator"textNO
A4:A10Cell Valuecontains "LFA"textNO
A4:A10Cell Valuecontains "UFC"textNO
C5:D9Cell Value<0textNO
C5:D9Cell Value>0textNO
A4,A5:B9Cell Valuecontains "!"textNO
 
Upvote 0
etaf said:
if you dont need rounding , and keeping J - then its done
in
J5 put
=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)
and copy down

In

G1 = Succesful Picks
=SUMIF(J5:J9,">1")

G2 = Unsuccesful Picks
=(SUMIF(J5:J9,"<1"))*-1

then you have the solution to the original question , how to get G1 & G2 , with J calculated by excel

bettor score (1).xlsx
ABCDEFGHIJK
1successful picks*based off 253*calculations to produce score per bet/pick based on a $100 bet
2unsuccessful picks$100 bet200* losses are straight -$100 loss, where as wins require formula
3fighter 1fighter2oddsodds2earningsearnings2bettor 1*I would prefer these points be calculated within G1 and G2 total ongoing score cells, not in their own column.
4LFA 120individual fight scores
5Mayra Cantuaria!Jamey Lyn Horth Wessels-125-105$80$95-1=-100
6!Tony LaramieTyus White-200160$50$1601=50
7Royce WhiteDaiqkwon Buckley !-150120$67$120-1=120
8Jalin Fuller!Bruno Assis-120-110$83$911=83.33333333
9!Felipe Martinez DonisHenry Huff-225185$44$185-1=-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
weekly betting analysis
Cell Formulas
RangeFormula
G1G1=SUMIF(J5:J9,">1")
G2G2=(SUMIF(J5:J9,"<1"))*-1
E5:E10E5=IF([@odds]<0,-10000/[@odds],[@odds])
F5:F10F5=IF([@odds2]<0,-10000/[@odds2],[@odds2])
J5:J9J5=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:A10Cell Valuecontains "bellator"textNO
A4:A10Cell Valuecontains "LFA"textNO
A4:A10Cell Valuecontains "UFC"textNO
C5:D9Cell Value<0textNO
C5:D9Cell Value>0textNO
A4,A5:B9Cell Valuecontains "!"textNO
Click to expand...
Awesome! We still got this "helper" column, so do we need it? Could it be integrated into the top G formulas? Or should I be asking "what is easier on Excel?"

I CAN just hide the helper row if need be. What's the most efficient way to do this if not what we got so far? Thanks Wayne
 
Upvote 0
etaf said:
if you dont need rounding , and keeping J - then its done
in
J5 put
=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)
and copy down

In

G1 = Succesful Picks
=SUMIF(J5:J9,">1")

G2 = Unsuccesful Picks
=(SUMIF(J5:J9,"<1"))*-1

then you have the solution to the original question , how to get G1 & G2 , with J calculated by excel

bettor score (1).xlsx
ABCDEFGHIJK
1successful picks*based off 253*calculations to produce score per bet/pick based on a $100 bet
2unsuccessful picks$100 bet200* losses are straight -$100 loss, where as wins require formula
3fighter 1fighter2oddsodds2earningsearnings2bettor 1*I would prefer these points be calculated within G1 and G2 total ongoing score cells, not in their own column.
4LFA 120individual fight scores
5Mayra Cantuaria!Jamey Lyn Horth Wessels-125-105$80$95-1=-100
6!Tony LaramieTyus White-200160$50$1601=50
7Royce WhiteDaiqkwon Buckley !-150120$67$120-1=120
8Jalin Fuller!Bruno Assis-120-110$83$911=83.33333333
9!Felipe Martinez DonisHenry Huff-225185$44$185-1=-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
weekly betting analysis
Cell Formulas
RangeFormula
G1G1=SUMIF(J5:J9,">1")
G2G2=(SUMIF(J5:J9,"<1"))*-1
E5:E10E5=IF([@odds]<0,-10000/[@odds],[@odds])
F5:F10F5=IF([@odds2]<0,-10000/[@odds2],[@odds2])
J5:J9J5=IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,F5,E5),-100)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:A10Cell Valuecontains "bellator"textNO
A4:A10Cell Valuecontains "LFA"textNO
A4:A10Cell Valuecontains "UFC"textNO
C5:D9Cell Value<0textNO
C5:D9Cell Value>0textNO
A4,A5:B9Cell Valuecontains "!"textNO
Click to expand...
if it wouldn't be less efficient to integrate them into the top G formulas that would be good cuz I got a lot of bettors I'm tracking which I'd have to add new helper columns to. But no matter, whatever is more efficient matters most
 
Upvote 0
Not managed to work out an array formula to do this without a helper

do you need 2 earnings columns ?
or
just a earnings columns for winnings and if a loss then use the -100

if so then instead of both column E and F
you could just have a column E - Earnings
=IF(A5="","",IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,IF(D5<0,-10000/D5,D5),IF(C5<0,-10000/C5,C5)),-100))

bettor score (1).xlsx
ABCDEF
1successful picks*based off 253
2unsuccessful picks$100 bet200
3fighter 1fighter2oddsodds2earningsbettor 1
4LFA 120
5Mayra Cantuaria!Jamey Lyn Horth Wessels-125-105-100-1
6!Tony LaramieTyus White-200160501
7Royce WhiteDaiqkwon Buckley !-150120120-1
8Jalin Fuller!Bruno Assis-120-11083.333333331
9!Felipe Martinez DonisHenry Huff-225185-100-1
10 *a "1" is a pick on the first fighter, a "-1" for the second fighter
11 
weekly betting analysis
Cell Formulas
RangeFormula
F1F1=SUMIF(E5:E9,">1")
F2F2=(SUMIF(E5:E9,"<1"))*-1
E5:E11E5=IF(A5="","",IF(OR(AND(ISNUMBER(FIND("!",A5,1)),F5=1), AND(ISNUMBER(FIND("!",B5,1)),F5=-1)),IF(F5=-1,IF(D5<0,-10000/D5,D5),IF(C5<0,-10000/C5,C5)),-100))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:A11Cell Valuecontains "bellator"textNO
A4:A11Cell Valuecontains "LFA"textNO
A4:A11Cell Valuecontains "UFC"textNO
C5:D9Cell Value<0textNO
C5:D9Cell Value>0textNO
A4,A5:B9Cell Valuecontains "!"textNO
 
Upvote 0
etaf said:
Not managed to work out an array formula to do this without a helper

do you need 2 earnings columns ?
or
just a earnings columns for winnings and if a loss then use the -100

if so then instead of both column E and F
you could just have a column E - Earnings
=IF(A5="","",IF(OR(AND(ISNUMBER(FIND("!",A5,1)),G5=1), AND(ISNUMBER(FIND("!",B5,1)),G5=-1)),IF(G5=-1,IF(D5<0,-10000/D5,D5),IF(C5<0,-10000/C5,C5)),-100))

bettor score (1).xlsx
ABCDEF
1successful picks*based off 253
2unsuccessful picks$100 bet200
3fighter 1fighter2oddsodds2earningsbettor 1
4LFA 120
5Mayra Cantuaria!Jamey Lyn Horth Wessels-125-105-100-1
6!Tony LaramieTyus White-200160501
7Royce WhiteDaiqkwon Buckley !-150120120-1
8Jalin Fuller!Bruno Assis-120-11083.333333331
9!Felipe Martinez DonisHenry Huff-225185-100-1
10 *a "1" is a pick on the first fighter, a "-1" for the second fighter
11 
weekly betting analysis
Cell Formulas
RangeFormula
F1F1=SUMIF(E5:E9,">1")
F2F2=(SUMIF(E5:E9,"<1"))*-1
E5:E11E5=IF(A5="","",IF(OR(AND(ISNUMBER(FIND("!",A5,1)),F5=1), AND(ISNUMBER(FIND("!",B5,1)),F5=-1)),IF(F5=-1,IF(D5<0,-10000/D5,D5),IF(C5<0,-10000/C5,C5)),-100))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:A11Cell Valuecontains "bellator"textNO
A4:A11Cell Valuecontains "LFA"textNO
A4:A11Cell Valuecontains "UFC"textNO
C5:D9Cell Value<0textNO
C5:D9Cell Value>0textNO
A4,A5:B9Cell Valuecontains "!"textNO
Click to expand...
=IF(A5="","". What's that comma in there for?

Thanks. Yeah, no need for two earnings columns. But wouldn't it be more efficient to have each bettor score refer to the single earnings column rather than include an earnings calculation under each bettor? I think I'll do that and put the helper columns on another sheet. That will be easier than individually adding them to each bettor then hiding them right?

I'm also thinking about replacing the flat -100 loss part of the earnings with a formulation to take into account the "opportunity cost" by not picking the winner, rather than just reflecting the $100 actual loss. I think that would be more an accurate reflection of the bettor's connection to potential value in the "ether" if you will. But I got that. If I run into any problems I'll follow up. Thanks Wayne.
 
Upvote 0
=IF(A5="","". What's that comma in there for?
Click to expand...
So you can copy the formula down the sheet , and if no entry in cell A , it puts a blank into the formula - so you dont get numbers appearing when you have no body fighting

OK, if you have a non related New question, probably better to start a new post, and then you will get other members answering , as we are now into post 37 replies, its unlikely anyone will read and reply - I dont, as i would expect the helper to be well into the reply/solution, I tend to look at 0 replies or maybe upto about 5 replies.

You may get an answer to your question on how to apply , the calculations without helpers, i dont see a solution , maybe VBA , which i dont answer questions on, as my VBA is from the 90's and out of date now.

Also you should update your profile with the version of excel you have, as that will make a lot of difference to the solution as 365 versions have a lot of functions not available in earlier versions
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Please help with unreliable bettor score formula
Replies
2
Views
317
juntjoo
J
M
  • Solved
Calculating NCAA pool score
Replies
3
Views
554
DRSteele
DRSteele
P
  • Question
Need a complicated VBA to send data from an entry sheet to another sheet based on a month.
Replies
1
Views
433
Pronzone
P
C
  • Question
Score sheets - select names once per sheet & produce running total from all score sheets
Replies
3
Views
2K
craigey1
C
B
  • Question
Best way to keep track of my games collection
Replies
1
Views
4K
maabadi
M

Forum statistics

Threads
1,214,998
Messages
6,122,639
Members
449,093
Latest member
Ahmad123098

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top