Formula - Filtered Data (Horse Racing)

Ali3ta1r

New Member
Joined
Nov 24, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVW
1DateTimeCourseHorseR-oddsPosOdds BSPLo FigRace NameTypeHandicapClassDistanceGoingRunnersSurfaceSTAKEN.BANKWINR.STAKE0.00
211/01/202015.15KemptonWaltSP8.237.20Try Unibet New Uniboosts Handicap ChaseChaseHandicap23mGood To Soft (Soft In Places)8Turf1.00-1.00-1.00
331/01/202016.45NewcastleElusive HeightsSP8.401.35Bombardier British Hopped Amber Beer Apprentice HandicapFlatHandicap61mStandard11Allweather1.00-2.00-2.00
414/03/202016.05NavanTokyo GetawaySP8.294.60Irish Stallion Farms EBF Novice Handicap Chase Final (Grade B)ChaseHandicap3mHeavy10Turf1.00-3.00-3.00
504/06/202013.00NewcastleGuipureSP1st8.061.01Heed Your Hunch At Betway HandicapFlatHandicap51mStandard10Allweather1.00-4.007.061.004.06
611/07/202014.40AscotSummit ReachSP8.007.00'Play Nifty Fifty Exclusively At Betfred' HandicapFlatHandicap21m 3fGood4Turf1.003.063.06
703/09/202015.40SalisburyTheotherside8.508.277.00Shadwell Duck Poole Fillies' Stakes (Group 3)FlatNon-Handicap16fGood (Good To Soft In Places)13Turf1.002.062.06
805/09/202013.45HaydockKinross11.008.191.44Betfair Superior Mile Stakes (Group 3)FlatNon-Handicap11mSoft (Heavy In Places)7Turf1.001.061.06
906/09/202015.40YorkBoma Green6.508.216.20Sky Bet Club HandicapFlatHandicap36fGood (Good To Soft In Places)16Turf1.000.060.06
1010/09/202013.00HaydockBinyon12.008.466.40Join Racing TV Now HandicapFlatHandicap51m 3fSoft (Good To Soft In Places)9Turf1.00-0.94-0.94
1110/09/202016.15DoncasterBreanski8.508.301.55Jaguar Land Rover Doncaster JCT600 HandicapFlatHandicap36fGood (Good To Soft In Places)14Turf1.00-1.94-1.94
1211/09/202018.50KilbegganThe Caddy Rose11.008.205.00Kilmurray's Homevalue Hardware Mullingar Handicap Chase (0-130)ChaseHandicap3m 1fYielding9Turf1.00-2.94-2.94
1313/09/202013.00BathFour Adaay7.008.202.06visitbath.co.uk NurseryFlatNon-Handicap55fGood To Firm (Firm In Places)6Turf1.00-3.94-3.94
1416/09/202016.35BeverleyMusharrif10.008.028.40Annie Oxtoby Memorial HandicapFlatHandicap45fGood11Turf1.00-4.94-4.94
1519/09/202016.00NewburyOur New Buddy9.008.246.20Heatherwold Stud EBF Novice Stakes (Str) (Div 2)FlatNon-Handicap57fGood14Turf1.00-5.94-5.94
1620/09/202015.00ChelmsfordSampers Seven7.008.113.35chelmsfordcityracecourse.com HandicapFlatHandicap25fStandard6Polytrack1.00-6.94-6.94
1721/09/202014.15WarwickCourt In Matera9.008.053.95Biffa Waste Services Novices' Handicap Hurdle (Div 2)HurdleHandicap52mGood (Good To Firm In Places)10Turf1.00-7.94-7.94
1822/09/202014.05BeverleyHarbour Vision5.508.407.20Eddie Moll HandicapFlatHandicap47fGood (Good To Firm In Places)9Turf1.00-8.94-8.94
1923/09/202016.55RedcarObee JoSP1st8.431.01Watch Race Replays At racingtv.com Handicap (Div 2)FlatHandicap65fGood To Soft (Soft In Places)13Turf1.00-9.947.431.00-1.51
2024/09/202015.55ListowelPersia5.508.205.00Listowel Printing Works HandicapFlatHandicap1m 4fHeavy12Turf1.00-2.51-2.51
2125/09/202016.35HaydockDancing Harry11.008.282.52Watch Racing Replays At racingtv.com HandicapFlatHandicap31m 6fGood To Soft8Turf1.00-3.51-3.51
2228/09/202014.50BathGlobal Prospector8.008.133.80Signs Express HandicapFlatHandicap45fFirm9Turf1.00-4.51-4.51
2330/09/202015.55BangorMarble Moon7.508.332.20Betsafe Racing From Over 40 Countries Handicap ChaseChaseHandicap43mGood (Good To Soft In Places)9Turf1.00-5.51-5.51
2402/10/202015.10GowranCapture The Action5.001st8.401.01Watch Irish Racing On Racing TV Handicap Hurdle (80-95)HurdleHandicap2mGood14Turf1.00-6.517.401.001.89
Odds 8.0 - 8.49
Cell Formulas
RangeFormula
T2:T24T2=W1-S2
S3:S24S3=S2
U5,U24,U19U5=S5*(G5-1)
V5,V24,V19V5=S5
W2:W24W2=T2+U2+V2
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
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
Excel Formula:
=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

New Member
Joined
Nov 24, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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

Board Regular
Joined
Jun 8, 2015
Messages
214
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

New Member
Joined
Nov 24, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 8, 2015
Messages
214
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,574
Office Version
  1. 365
Platform
  1. Windows
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

Cell Formulas
RangeFormula
S2:S24S2=IF(SUBTOTAL(3,D2),1,"")
T2:T24T2=IF(S2="","",LOOKUP(9^9,W$1:W1-S2))
U2:U24U2=IF(S2="","",IF(F2="1st",S2*(G2-S2),""))
V2:V24V2=IF(U2="","",S2)
W2:W24W2=IF(S2="","",SUM(T2:V2))
 

Ali3ta1r

New Member
Joined
Nov 24, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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

Cell Formulas
RangeFormula
S2:S24S2=IF(SUBTOTAL(3,D2),1,"")
T2:T24T2=IF(S2="","",LOOKUP(9^9,W$1:W1-S2))
U2:U24U2=IF(S2="","",IF(F2="1st",S2*(G2-S2),""))
V2:V24V2=IF(U2="","",S2)
W2:W24W2=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,574
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. :)
 

Forum statistics

Threads
1,176,098
Messages
5,901,381
Members
434,887
Latest member
zoath

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
Top