Excel Help Needed

[lindemann]

It's very simple using BA to put a whole day's racing info into a single spreadsheet but is it then possible to write formulae that will recognise each individual race within the spreadsheet?

As a simple example, if the first race has 10 runners in rows 5 to 14 then I can rank by last price matched by copying =RANK(O5,$O$5:$O$14,1) into these rows. If I copied the formula down the whole spreadsheet, I'd have to laboriously update all the row references for each race one by one.

What I think I'm after is an ingenious way to automatically recognise each grouping of data as a separate race and maybe use something like =RANK(O5,RACE1,1) where RACE1, RACE2, etc.... are named ranges identifying the relevant data.

Can this be done in any way?

[osknows]

Hello,

Yes it can be done but it may significantly slow down the calculation time for the sheet due to volatile calculations in Excel. It depends on the number of rows and may be quicker to use VBA. Anyway the link below shows an example of how it could be done using just Excel formulae

http://www.mediafire.com/?5acb1bz2sbzw16t

Column Q is used to define a unique count for each block.
Column R is non essential but shows how I've got the range address for the blocks
Column S combines the formula in column R with the RANK function

Os

[lindemann]

Osknows,

Brilliant, as usual! That's exactly what I want and calc time isn't a problem as I should have mentioned that once I've populated the sheet I then break the link to BA, re-link to another sheet (called Market) and use the original sheet (called Ratings) as a static lookup table when populated with all relevant data.

I hope that makes sense but thanks again for the first class help.

By the way, one of the many calcs I do in Ratings is one of Entropy as mentioned by Doris Day in another thread. I've had some interesting early observations on this approach although I'm still some way from my first million!

[mickemick]

I know my question may be stupid for some of you but I am just starting my adventure with Excel/Gruss

Can you explain how to log whole days races into a single sheet ? Let say I want to have Monday's races on a single Excel sheet with odds and rank for each horse ?

[lindemann]

mick,

Assuming you know how to get the day's races into the Quick Pick List (if not, please ask) you just right click in the QP window and select the "Add all markets to current tab" option. If you now link to an Excel sheet (once again, just ask if you need help here), the whole day's racing will be linked to the sheet.

The drawback to this is that the refresh rate is very sluggish with all markets continuously updating and Excel calculations would also be slow. I have my own way of working round this as mentioned above, others may have better ways.

[mickemick]

Thanx lindemann,

It was really easy...done it and it works

[lindemann]

Gary,

I've come across a little glitch when loading a full day's racing into Excel. With all markets in the current tab, I link to my Excel sheet and select 'Official Ratings' as the only additional column. Columns A to Y of my spreadsheet are then populated, albeit slowly as many refreshes are needed to get all of the data.

When I unselect 'Log Current Prices' however, some of the data in Columns X and Y is overwritten/scrambled. It only happens to the first few races at the top of the spreadsheet, everything below is fine. I can manually correct the races of interest to me but I thought I'd let you know as it seems to be a glitch in an otherwise perfect process.

Any ideas?

[GaryRussell]

This rings a bell. It sounds like a problem I fixed a while back in the Beta version, see http://www.gruss-software.co.uk/forum/v ... php?t=5137

Can you try the Beta version and let me know if you still have the problem. See http://www.gruss-software.co.uk/forum/v ... php?t=5832 for download link.

[lindemann]

That seems to have worked perfectly on today's racing Gary. I'll continue using Beta and if you hear nothing everything is OK. Thanks for a quick and efficient response (again).