OZmium Sports Betting and Horse Racing Forums

OZmium Sports Betting and Horse Racing Forums (http://forums.ozmium.com.au/index.php)

- Horse Race Betting Systems (http://forums.ozmium.com.au/forumdisplay.php?f=10)

- - Excel Formula help (http://forums.ozmium.com.au/showthread.php?t=18872)

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.

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.

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.

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?

Quote:

Originally Posted by lomaca

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?

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.

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.

How can i import this text doc to excel but delete the gaps between each line

Problem solved with a good text editor