Sports Tipping Formula!

[Mully]

If your up for a challenge please have a go at this formula as I'm stumped


If you can imagine there are a bunch of sports games played during the week,
often with multiple games played on the same day.

What I want to do is to create a formula that will select only ONE game per
date that best meets the parameters I have set to place a bet on and to keep
count of the amount of bets I place before I get a WIN!
The formula will display the amount of bets placed Including the win in
Column F.

If you view the sample data and results below you will see Ive added notes
in brackets to describe how the formula would work.
It shows that it took 3 legitimate bets (incl win) before I posted my first
win, hence "3" is displayed in column F.
It takes another 5 legitimate bets before I get my next win, so "5" is
displayed.
The the very next bet I get another win straight up, so "1" is displayed.

The parameters for 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
=============

COLUMN HEADERS A to 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 - criteria met)
H Team_b 30/12/07 $1.47 WON - (no cnt same date & higher odds)
H Team_d 01/01/08 $1.45 WON - (2nd cnt - criteria met)
A Team_e 02/01/08 $1.54 WON - (no cnt - away game)
H Team_f 02/01/08 $1.30 WON - (no cnt as < $1.40)
H Team_g 03/01/08 $1.50 WON - "3" IS DISPLAYED
H Team_h 03/01/08 $1.52 WON - (no cnt as same date & higher odds)
H Team_i 20/01/08 $1.53 DRAW - (1st cnt - criteria met)
H Team_k 22/01/08 $1.53 WON - "2" IS DISPLAYED
H Team_l 30/01/08 $1.45 DRAW - (1st cnt - criteria met)
H Team_m 31/01/08 $1.42 LOSS - (2nd cnt - criteria met)
H Team_n 01/02/08 $1.57 WON - "3" IS DISPLAYED
H Team_o 05/03/08 $1.45 WON - "1" IS DISPLAYED


So far another very 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$12>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 WINS on same date if parameters met
when it should just choose the one WIN that has lowest odds or if same odds
then lowest alphabet ranking for the team name.

 

[Mully]

Ive re-worded my question to make it a bit more understandable.

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$12>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).