Smartgambler
Pro-Punter

Go Back   OZmium Sports Betting and Horse Racing Forums > Public Forums > Horse Race Betting Systems
Reload this Page Excel Formula help
User Name
Password
Register FAQ Search Today's Posts Mark all topics as read

To advertise on these
forums, e-mail us.

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 15th May 2009, 09:49 AM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,408
Default Excel Formula help
In the excel sheet i have some sample betfair data, what i need to do is extract the favourite from each race can any one help with a formula to do this.
Attached Files
File Type: xls New Microsoft Excel Worksheet.xls (24.0 KB, 585 views)
Reply With Quote
  #2  
Old 15th May 2009, 10:11 AM
goty0405 goty0405 is offline
Member
  
Join Date: Mar 2009
Posts: 47
Default
I haven't had time to look at your spreadsheet but a pretty simple way would be to do something like this:

Add a column called "Favourite"
Write a formula in tat column that gets the Minimum price of all runners, and then compare each runner's price to that. Something like this:
=IF(A1=(MIN(A$1:A$20)),1,0)

Where A1 = the price of the runner
A$1:A$20 is the series of prices of all runners

Put that formula in each cell in the Favourite column. That would put a 1 in the Favourite column if they are the favourite, or a 0 if they are not.
Reply With Quote
  #3  
Old 15th May 2009, 10:38 AM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,408
Default
I can use formulas to extract this information in the below excel doc so now i need to get the fave from each race, i have listed 3 races here.
Attached Files
File Type: xls New Microsoft Excel Worksheet.xls (13.5 KB, 547 views)
Reply With Quote
  #4  
Old 15th May 2009, 10:50 AM
lomaca lomaca is offline
Member
  
Join Date: Jan 1970
Posts: 1,096
Default
Quote:
Originally Posted by Shaun
In the excel sheet i have some sample betfair data, what i need to do is extract the favourite from each race can any one help with a formula to do this.
There is no simple formula, you will have to write a VBA script to loop through all the lines, select the course AND the race number starting from race one and then sort that group by price. If you use a select statement you can include the sort function into it.

You will need a double loop, first for the course, and an inner loop for the race number.

I haven't used VBA for many years, so I cant help you with that, the closest I can come with is VB which is similar, but I think someone out there is an Excel guru and will be faster, Chrome P maybe?
Reply With Quote
  #5  
Old 15th May 2009, 12:06 PM
lomaca lomaca is offline
Member
  
Join Date: Jan 1970
Posts: 1,096
Default
Quote:
Originally Posted by lomaca
There is no simple formula, you will have to write a VBA script to loop through all the lines, select the course AND the race number starting from race one and then sort that group by price. If you use a select statement you can include the sort function into it.

You will need a double loop, first for the course, and an inner loop for the race number.

I haven't used VBA for many years, so I cant help you with that, the closest I can come with is VB which is similar, but I think someone out there is an Excel guru and will be faster, Chrome P maybe?
On second thoughts, I suppose you could put a formula in a new column, that would compare the course and race number in the following record and until they are the same it would write the price in a cell if it is less then the cell before.
But you would have to make the comparison for the course and when the course name changes assign a new cell to write the smallest price into. It's a round-about way of what I said before, it might even work?
Reply With Quote
  #6  
Old 15th May 2009, 04:17 PM
Chrome Prince Chrome Prince is offline
Member
  
Join Date: Jan 1970
Posts: 4,417
Default
For the purposes of simplicity, I'll explain, but you can record a macro afterwards to make it easy next time.

Data > Sort

By column E Ascending and H Descending

In column I2 =IF(B1&C1<>B2&C2,"favourite","")

Copy formula all the way down Column I

Favourite should be written in Column I for favourite, or blank if it's not.

Then you can Data Filter Autofilter and just select favourites from column I, or use a formula to add them up or whatever.
__________________
RaceCensus - powerful system testing software.
Now with over 415,000 Metropolitan, Provincial and Country races!
http://www.propun.com.au/horse_raci...ng_systems.html
*RaceCensus now updated to 28/02/2025
Video overview of RaceCensus here:
http://www.youtube.com/watch?v=W821YP_b0Pg
Reply With Quote
  #7  
Old 15th May 2009, 05:50 PM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,408
Default
Thanks for all the help, i got it sorted over at Mr Excel if you look at the sheet below in the blue section you will see what i used.
Attached Files
File Type: xls New Microsoft Excel Worksheet.xls (48.0 KB, 568 views)
Reply With Quote
  #8  
Old 15th May 2009, 07:02 PM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,408
Default
How can i import this text doc to excel but delete the gaps between each line
Attached Files
File Type: txt New Text Document.txt (754 Bytes, 519 views)
Reply With Quote
  #9  
Old 15th May 2009, 09:13 PM
Shaun Shaun is offline
Member
  
Join Date: Jan 1970
Location: Western Australia
Posts: 3,408
Default
Problem solved with a good text editor
Reply With Quote
Reply

« Previous Thread | Next Thread »

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is On
Forum Jump



All times are GMT +10. The time now is 11:47 PM.

Contact Us - OZmium Pty Ltd - Archive - Terms of Use - Privacy Statement - Top

Powered by: vBulletin Version 3.0.3
Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
©2008 OZmium Pty. Ltd. All rights reserved . ACN 091184655