M
Mully
If your up for a challenge please have a go at this formula as I'm stumped!
What I want to do is create a formula that will analyze a historical table
of sports games and count how many bets it takes to record a win from a set
of rules that I define.
The bets are only counted if it meets this set of rules, otherwise it doesnt
get counted. Also only one bet can be counted per unique date so if more
than two games meet the rules for a bet on a particular date, than it is the
game with the lower odds (ColD) that gets counted.
I have included the data table below from columns A - E. Column F is where
the formula displays the bets counted when a win takes place that meets my
rules.
I have included comments in brackets for column F which are not part of the
formula, its simply to explain how the formula would interpret the data shown
from columns A - E.
However the data in column F with a number enclosed in " " is the data I
want the formula to display.
As you can see from the table below, it took 3 bets (including the win)
before I posted my first win, hence the number "3" is displayed in column F.
It takes another 2 bets before I get my next win, so number "2" is displayed.
The rules for counting a bet are as follows.
* Have to be the lowest odds (ColD) but > than $1.40 and < $1.60.
* Be a home game (ColA).
* There can only be one bet per unique date, lowest odds is selected.
* In the advent of same odds & same date, the team with the lowest
alphabetical ranking (ColB) should be counted.
SAMPLE DATA & RESULTS COLUMN
============================
A==B=======C=========D======E======F===============================
H Team_a 30/12/07 $1.70 LOSS - (no cnt as > $1.60)
H Team_b 30/12/07 $1.42 DRAW - (1st cnt - rules met)
H Team_b 30/12/07 $1.47 WIN - (no cnt same date & higher odds)
H Team_d 01/01/08 $1.45 WIN - (2nd cnt - rules met)
A Team_e 02/01/08 $1.54 WIN - (no cnt - away game)
H Team_f 02/01/08 $1.30 WIN - (no cnt as < $1.40)
H Team_g 03/01/08 $1.50 WIN - "3"
H Team_h 03/01/08 $1.52 WIN - (no cnt as same date & higher odds)
H Team_i 20/01/08 $1.53 DRAW - (1st cnt - rules met)
H Team_k 22/01/08 $1.53 WON - "2"
So far another helpful person has come up with this formula
=IF(A2="H",IF(E2="WON",IF((D2>1.4)*(D2<1.6),
SUM(IF(FREQUENCY(IF(($A$1:A2="H")*($D$1
2>1.4)*($D$12<1.6)*
($C$1:C2<>"")*($E$1:E2<>"WON"),MATCH("~"&$C$1:C2,$C$1:C2&"",0)),
ROW($C$1:C2)-ROW($C$1)+1),1))-SUM($K$1:K1),""),""),"")
It almost works, but it counts multiple bets on same date when they are both
wins when it should just choose the one win that has lowest odds or if same
odds then the lowest alphabet ranking for the team name (ColB).
What I want to do is create a formula that will analyze a historical table
of sports games and count how many bets it takes to record a win from a set
of rules that I define.
The bets are only counted if it meets this set of rules, otherwise it doesnt
get counted. Also only one bet can be counted per unique date so if more
than two games meet the rules for a bet on a particular date, than it is the
game with the lower odds (ColD) that gets counted.
I have included the data table below from columns A - E. Column F is where
the formula displays the bets counted when a win takes place that meets my
rules.
I have included comments in brackets for column F which are not part of the
formula, its simply to explain how the formula would interpret the data shown
from columns A - E.
However the data in column F with a number enclosed in " " is the data I
want the formula to display.
As you can see from the table below, it took 3 bets (including the win)
before I posted my first win, hence the number "3" is displayed in column F.
It takes another 2 bets before I get my next win, so number "2" is displayed.
The rules for counting a bet are as follows.
* Have to be the lowest odds (ColD) but > than $1.40 and < $1.60.
* Be a home game (ColA).
* There can only be one bet per unique date, lowest odds is selected.
* In the advent of same odds & same date, the team with the lowest
alphabetical ranking (ColB) should be counted.
SAMPLE DATA & RESULTS COLUMN
============================
A==B=======C=========D======E======F===============================
H Team_a 30/12/07 $1.70 LOSS - (no cnt as > $1.60)
H Team_b 30/12/07 $1.42 DRAW - (1st cnt - rules met)
H Team_b 30/12/07 $1.47 WIN - (no cnt same date & higher odds)
H Team_d 01/01/08 $1.45 WIN - (2nd cnt - rules met)
A Team_e 02/01/08 $1.54 WIN - (no cnt - away game)
H Team_f 02/01/08 $1.30 WIN - (no cnt as < $1.40)
H Team_g 03/01/08 $1.50 WIN - "3"
H Team_h 03/01/08 $1.52 WIN - (no cnt as same date & higher odds)
H Team_i 20/01/08 $1.53 DRAW - (1st cnt - rules met)
H Team_k 22/01/08 $1.53 WON - "2"
So far another helpful person has come up with this formula
=IF(A2="H",IF(E2="WON",IF((D2>1.4)*(D2<1.6),
SUM(IF(FREQUENCY(IF(($A$1:A2="H")*($D$1
2>1.4)*($D$12<1.6)*($C$1:C2<>"")*($E$1:E2<>"WON"),MATCH("~"&$C$1:C2,$C$1:C2&"",0)),
ROW($C$1:C2)-ROW($C$1)+1),1))-SUM($K$1:K1),""),""),"")
It almost works, but it counts multiple bets on same date when they are both
wins when it should just choose the one win that has lowest odds or if same
odds then the lowest alphabet ranking for the team name (ColB).