Formula - Filtered Data (Horse Racing)

[Ali3ta1r]

Hi,

Hoping someone can help.

So firstly a small introduction... my hobby is horse racing and I like to bet (small stakes) and I keep a record of my betting history on Excel.

Currently, when I wish to analyse my betting and the profits/loss I have to filter the data, copy and paste to a separate sheet and then run my calculations. What I would like to be able to do is query/filter my betting history data based on various criteria (i.e. odds, distance, number of runners etc) on the one Master sheet, and then the profit/loss calculations to update accordingly (Columns S, T, U & W). Though I can't seem to be able to do this.

Is this possible?

Finally, if not... would there be a formula I can use that would work based purely on filtering the data by BSP odds (Column G)? Such as, filtering odds so only results with numbers/odds greater than 8.0.

I have put an example below of the Excel sheet which I maintain. Any questions please ask!

UPWORK ALL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Date	Time	Course	Horse	R-odds	Pos	Odds BSP	Lo Fig		Race Name	Type	Handicap	Class	Distance	Going	Runners	Surface		STAKE	N.BANK	WIN	R.STAKE	0.00
2	11/01/2020	15.15	Kempton	Walt	SP		8.23	7.20		Try Unibet New Uniboosts Handicap Chase	Chase	Handicap	2	3m	Good To Soft (Soft In Places)	8	Turf		1.00	-1.00			-1.00
3	31/01/2020	16.45	Newcastle	Elusive Heights	SP		8.40	1.35		Bombardier British Hopped Amber Beer Apprentice Handicap	Flat	Handicap	6	1m	Standard	11	Allweather		1.00	-2.00			-2.00
4	14/03/2020	16.05	Navan	Tokyo Getaway	SP		8.29	4.60		Irish Stallion Farms EBF Novice Handicap Chase Final (Grade B)	Chase	Handicap		3m	Heavy	10	Turf		1.00	-3.00			-3.00
5	04/06/2020	13.00	Newcastle	Guipure	SP	1st	8.06	1.01		Heed Your Hunch At Betway Handicap	Flat	Handicap	5	1m	Standard	10	Allweather		1.00	-4.00	7.06	1.00	4.06
6	11/07/2020	14.40	Ascot	Summit Reach	SP		8.00	7.00		'Play Nifty Fifty Exclusively At Betfred' Handicap	Flat	Handicap	2	1m 3f	Good	4	Turf		1.00	3.06			3.06
7	03/09/2020	15.40	Salisbury	Theotherside	8.50		8.27	7.00		Shadwell Duck Poole Fillies' Stakes (Group 3)	Flat	Non-Handicap	1	6f	Good (Good To Soft In Places)	13	Turf		1.00	2.06			2.06
8	05/09/2020	13.45	Haydock	Kinross	11.00		8.19	1.44		Betfair Superior Mile Stakes (Group 3)	Flat	Non-Handicap	1	1m	Soft (Heavy In Places)	7	Turf		1.00	1.06			1.06
9	06/09/2020	15.40	York	Boma Green	6.50		8.21	6.20		Sky Bet Club Handicap	Flat	Handicap	3	6f	Good (Good To Soft In Places)	16	Turf		1.00	0.06			0.06
10	10/09/2020	13.00	Haydock	Binyon	12.00		8.46	6.40		Join Racing TV Now Handicap	Flat	Handicap	5	1m 3f	Soft (Good To Soft In Places)	9	Turf		1.00	-0.94			-0.94
11	10/09/2020	16.15	Doncaster	Breanski	8.50		8.30	1.55		Jaguar Land Rover Doncaster JCT600 Handicap	Flat	Handicap	3	6f	Good (Good To Soft In Places)	14	Turf		1.00	-1.94			-1.94
12	11/09/2020	18.50	Kilbeggan	The Caddy Rose	11.00		8.20	5.00		Kilmurray's Homevalue Hardware Mullingar Handicap Chase (0-130)	Chase	Handicap		3m 1f	Yielding	9	Turf		1.00	-2.94			-2.94
13	13/09/2020	13.00	Bath	Four Adaay	7.00		8.20	2.06		visitbath.co.uk Nursery	Flat	Non-Handicap	5	5f	Good To Firm (Firm In Places)	6	Turf		1.00	-3.94			-3.94
14	16/09/2020	16.35	Beverley	Musharrif	10.00		8.02	8.40		Annie Oxtoby Memorial Handicap	Flat	Handicap	4	5f	Good	11	Turf		1.00	-4.94			-4.94
15	19/09/2020	16.00	Newbury	Our New Buddy	9.00		8.24	6.20		Heatherwold Stud EBF Novice Stakes (Str) (Div 2)	Flat	Non-Handicap	5	7f	Good	14	Turf		1.00	-5.94			-5.94
16	20/09/2020	15.00	Chelmsford	Sampers Seven	7.00		8.11	3.35		chelmsfordcityracecourse.com Handicap	Flat	Handicap	2	5f	Standard	6	Polytrack		1.00	-6.94			-6.94
17	21/09/2020	14.15	Warwick	Court In Matera	9.00		8.05	3.95		Biffa Waste Services Novices' Handicap Hurdle (Div 2)	Hurdle	Handicap	5	2m	Good (Good To Firm In Places)	10	Turf		1.00	-7.94			-7.94
18	22/09/2020	14.05	Beverley	Harbour Vision	5.50		8.40	7.20		Eddie Moll Handicap	Flat	Handicap	4	7f	Good (Good To Firm In Places)	9	Turf		1.00	-8.94			-8.94
19	23/09/2020	16.55	Redcar	Obee Jo	SP	1st	8.43	1.01		Watch Race Replays At racingtv.com Handicap (Div 2)	Flat	Handicap	6	5f	Good To Soft (Soft In Places)	13	Turf		1.00	-9.94	7.43	1.00	-1.51
20	24/09/2020	15.55	Listowel	Persia	5.50		8.20	5.00		Listowel Printing Works Handicap	Flat	Handicap		1m 4f	Heavy	12	Turf		1.00	-2.51			-2.51
21	25/09/2020	16.35	Haydock	Dancing Harry	11.00		8.28	2.52		Watch Racing Replays At racingtv.com Handicap	Flat	Handicap	3	1m 6f	Good To Soft	8	Turf		1.00	-3.51			-3.51
22	28/09/2020	14.50	Bath	Global Prospector	8.00		8.13	3.80		Signs Express Handicap	Flat	Handicap	4	5f	Firm	9	Turf		1.00	-4.51			-4.51
23	30/09/2020	15.55	Bangor	Marble Moon	7.50		8.33	2.20		Betsafe Racing From Over 40 Countries Handicap Chase	Chase	Handicap	4	3m	Good (Good To Soft In Places)	9	Turf		1.00	-5.51			-5.51
24	02/10/2020	15.10	Gowran	Capture The Action	5.00	1st	8.40	1.01		Watch Irish Racing On Racing TV Handicap Hurdle (80-95)	Hurdle	Handicap		2m	Good	14	Turf		1.00	-6.51	7.40	1.00	1.89
Odds 8.0 - 8.49

Cell Formulas
Range	Formula
T2:T24	T2	=W1-S2
S3:S24	S3	=S2
U5,U24,U19	U5	=S5*(G5-1)
V5,V24,V19	V5	=S5
W2:W24	W2	=T2+U2+V2

 

[Toadstool]

Hi Ali3ta1r,

I'm not sure if I follow completely and I only have Excel 2016 whereas your 365 Windows version should have new functions such as FILTER which may answer your question... but let me at least try a suggestion.

Some functions, such as SUBTOTAL and AGGREGATE can ignore hidden rows. I don't know what calculations you do so let me try AGGREGATE on a new sheet. In A1 of the new sheet paste

=INDEX('Odds 8.0 - 8.49'!A$1:A$9999,AGGREGATE(15,7,ROW('Odds 8.0 - 8.49'!$A$1:$A$9999),ROW()))

then copy it across and down as far as you want (and you can, of course, change the range 9999 to your required number).

This will populate the new sheet with all cells from the master, but the option 7 in that statement ignores error and hidden rows. Now filter the master how you want and only the visible rows will appear in the new sheet.

Hope that helps.

 

[Ali3ta1r]

Hi Ali3ta1r,

I'm not sure if I follow completely and I only have Excel 2016 whereas your 365 Windows version should have new functions such as FILTER which may answer your question... but let me at least try a suggestion.

Some functions, such as SUBTOTAL and AGGREGATE can ignore hidden rows. I don't know what calculations you do so let me try AGGREGATE on a new sheet. In A1 of the new sheet paste

=INDEX('Odds 8.0 - 8.49'!A$1:A$9999,AGGREGATE(15,7,ROW('Odds 8.0 - 8.49'!$A$1:$A$9999),ROW()))

then copy it across and down as far as you want (and you can, of course, change the range 9999 to your required number).

This will populate the new sheet with all cells from the master, but the option 7 in that statement ignores error and hidden rows. Now filter the master how you want and only the visible rows will appear in the new sheet.

Hope that helps.

So I will try explain better what my typical calculations are:

Columns A to Q = Just the race/horse details. For the calculations, none of them are important apart from Column G (BSP Odds) which is a figure used calculating the potential winnings (see below).
Columns S to W = The Profit/Loss calculations which I will detail further below.

CALCULATIONS
Column S = Is the stake of the bet (which is always to a level stake of 1.0 pt)
Column T = Is the bank after the bet has been made (always -1.0pt from the running bank total, see below)
Column U = The returns if the bet won (listed as 1st in Row F). So for example, Row 5 the horse Guipure came 1st. So the potential winnings are the stake of 1pt (Column S) multiplied by the BSP Odds (Column G) minus 1 (we have to minus the 1 to get the right amount).
Column V = If the bet won, we also get our initial stake returned. This is always 1pt (Column S)
Column W = The running bank total (Cell W1 = the starting bank of 0.00pts). If a bet wins, it will be Column T (post 1pt stake) plus the winnings (Column U) and returned stake (Column V). If a bet loses, it will simply be the running bank minus the stake (Column T).

With the above being said, let me explain further my issue.

FILTERING DATA
So with the above explained above. I now wish to expand on Columns A to Q as these are the data which I want to filter and then the calculations in Columns S to W will update based on those cells/data which are visible. At the moment, when I filter the cells/data in Columns A to Q they are still taken into consideration in the profit/loss calculations in Columns S to W.

WHAT I WOULD LIKE TO DO
So for example, here is a potential query which I would like to run.

I want to only show horse races that:

1. have BSP Odds (Column G) that are equal to or above 8.6
2. Are a Flat type race (Column K)
3. Going that doesn't include "Heavy" in the text.

Once I have filtered the data, I would like the profit/loss calculations (Column S to W) to update based on these filtered results (at the moment they don't).

MY QUESTION

Is it possible to have a more dynamic way of processing and analyzing my betting and profitability (i.e. the caluclations in Columns S to W) on the one Master sheet? In either Excel or any other tool.

 

[johnny51981]

I use a helper column in each of my reports' DATA sheet/table that uses a function in the Name Manager. Here are the steps.

Create a Column in the sheet that houses the data, I usually have this in Column A and place a header in cell A1 called "SHOW"
With your active cell still being A1 of your data sheet, create a new "Name" in Name Manager, I name this as "ShowThisRow" and populate the Refers To: with "=GET.CELL(17,'TASK DATA'!$A1)>0" (no quotes).
Then in Cell A2, simply enter "=ShowThisRow()" (again, no quotes), and see that it is included all the way down to the bottom of your data. If this is housed in a Table, it will fill down with each new row added.
What this is going to do is say TRUE if the Cell/Row Height is greater than 0 or FALSE if the Cell/Row Height is 0. But you won't be able to see the FALSEs due to your different filters in place.
Then, on your processing/analyzing, include an additional criteria to each of your formulas to include "TRUE" in that "SHOW" column. With each AutoFilter change, the results will update without any additional hands on change.

 

[Ali3ta1r]

I use a helper column in each of my reports' DATA sheet/table that uses a function in the Name Manager. Here are the steps.

Create a Column in the sheet that houses the data, I usually have this in Column A and place a header in cell A1 called "SHOW"
With your active cell still being A1 of your data sheet, create a new "Name" in Name Manager, I name this as "ShowThisRow" and populate the Refers To: with "=GET.CELL(17,'TASK DATA'!$A1)>0" (no quotes).
Then in Cell A2, simply enter "=ShowThisRow()" (again, no quotes), and see that it is included all the way down to the bottom of your data. If this is housed in a Table, it will fill down with each new row added.
What this is going to do is say TRUE if the Cell/Row Height is greater than 0 or FALSE if the Cell/Row Height is 0. But you won't be able to see the FALSEs due to your different filters in place.
Then, on your processing/analyzing, include an additional criteria to each of your formulas to include "TRUE" in that "SHOW" column. With each AutoFilter change, the results will update without any additional hands on change.

Hi Johnny,

This sounds great. However, I am a bit lost.

I created the Name Manager and populated the Refers to.

Then I entered the =ShowThisRow() in A2. It opens a window for browsing for a file on my PC, is this right?

I copied this from A2 right down to the bottom of my populated data.

Then I am a little confused on how to include the TRUE/SHOW in the formulas I use for calculating my profit/loss. Could you please explain in a little more detail?

Thanks,
Alistair

 

[johnny51981]

I made an error, apologies. It is just =ShowThisRow (no parenthesis). No window for browsing should appear, this could have been due to the parenthesis.

Anytime you have a Countif or a Sumif, change it to Countifs or Sumifs. So for a simple example:
Old: =Countif([Course],"Kempton")
New: =Countifs([Show],"TRUE",[Course],"Kempton")

 

[Peter_SSs]

Make a copy of your worksheet, ensure that it is unfiltered and try these formulas in columns S:W then try filtering any of columns A:Q

Ali3ta1r.xlsm
	S	T	U	V	W
1	STAKE	N.BANK	WIN	R.STAKE	0
2	1	-1.00			-1.00
3	1	-2.00			-2.00
4	1	-3.00			-3.00
5	1	-4.00	7.06	1	4.06
6	1	3.06			3.06
7	1	2.06			2.06
8	1	1.06			1.06
9	1	0.06			0.06
10	1	-0.94			-0.94
11	1	-1.94			-1.94
12	1	-2.94			-2.94
13	1	-3.94			-3.94
14	1	-4.94			-4.94
15	1	-5.94			-5.94
16	1	-6.94			-6.94
17	1	-7.94			-7.94
18	1	-8.94			-8.94
19	1	-9.94	7.43	1	-1.51
20	1	-2.51			-2.51
21	1	-3.51			-3.51
22	1	-4.51			-4.51
23	1	-5.51			-5.51
24	1	-6.51	7.4	1	1.89
Calculate on filtered rows

Cell Formulas
Range	Formula
S2:S24	S2	=IF(SUBTOTAL(3,D2),1,"")
T2:T24	T2	=IF(S2="","",LOOKUP(9^9,W$1:W1-S2))
U2:U24	U2	=IF(S2="","",IF(F2="1st",S2*(G2-S2),""))
V2:V24	V2	=IF(U2="","",S2)
W2:W24	W2	=IF(S2="","",SUM(T2:V2))

 

[Ali3ta1r]

Make a copy of your worksheet, ensure that it is unfiltered and try these formulas in columns S:W then try filtering any of columns A:Q

Ali3ta1r.xlsm
	S	T	U	V	W
1	STAKE	N.BANK	WIN	R.STAKE	0
2	1	-1.00			-1.00
3	1	-2.00			-2.00
4	1	-3.00			-3.00
5	1	-4.00	7.06	1	4.06
6	1	3.06			3.06
7	1	2.06			2.06
8	1	1.06			1.06
9	1	0.06			0.06
10	1	-0.94			-0.94
11	1	-1.94			-1.94
12	1	-2.94			-2.94
13	1	-3.94			-3.94
14	1	-4.94			-4.94
15	1	-5.94			-5.94
16	1	-6.94			-6.94
17	1	-7.94			-7.94
18	1	-8.94			-8.94
19	1	-9.94	7.43	1	-1.51
20	1	-2.51			-2.51
21	1	-3.51			-3.51
22	1	-4.51			-4.51
23	1	-5.51			-5.51
24	1	-6.51	7.4	1	1.89
Calculate on filtered rows

Cell Formulas
Range	Formula
S2:S24	S2	=IF(SUBTOTAL(3,D2),1,"")
T2:T24	T2	=IF(S2="","",LOOKUP(9^9,W$1:W1-S2))
U2:U24	U2	=IF(S2="","",IF(F2="1st",S2*(G2-S2),""))
V2:V24	V2	=IF(U2="","",S2)
W2:W24	W2	=IF(S2="","",SUM(T2:V2))

Fantastic - this works a treat!

Now I just need to find a way to make Excel run quicker with these formulas in the page.

Thanks for the help!

 

[Peter_SSs]

You're welcome. Glad we could help.