I'm after some help on applying a formula to a database, that returns the % change in Odds from Open to Close. Maybe walkermac, Excel Cup 2014 winner, could help?

From the attachment, you have varying numbers of fluctuations for each runner.
For example:
Golden Sally Open = 5.3, Close 8. Therefore % change = 150%
Nuclear Power Open = 9.8, Close = 14. Therefore % change = 143%

I could put the database into a pivot table but there's no standard function to carry out this calculation, only Sum, Count, Average, Max, Min, Product, Stdev & Var.

This new function if possible (hopefully not in VBA) would allow me to quickly scan large databases and look for correlations in steamers/drifters and their strike rates.

Thanks in anticipation.

Attached Files

% Change Query.xlsx (12.7 KB, 1246 views)

Hi Rinconpaul,

I know this gives you a percentage change =(A1/B1)-1

But probably not what your after.

Shaun or someone would definitely be able to help you I'm sure.

Thanks Toil. Yes it's easy enough to work out on a case by case basis, the difficulty is the number of rows for each runner is forever changing, so the formula needs to be intuitive and know when it's a different runner and be able to count back to the beginning to find out what the first odds value was. It needs to be automatic, as tens of thousands of entries to assess.

Yer, like I said probably not what your after

If you can get MS Query to work (I tried but it doesn't want to play nice) you could do this using SQL - something like:

SELECT MAX(course), MAX(event), (MAX(odds) / MIN(odds)) FROM [table_name]
GROUP BY event_id, selection

--------------

Or you could import the spreadsheet into a database and then run the query.

Just re-read your question. the above won't do what you asked...

I'd need to have a think whether a single SQL statement will do it.

Since when do % changes equal what you say here? Surely they are 50% and 43% changes. A 100% change would be double the price, wouldn't it? Or am I missing something?

__________________
Never give up on a dream just because of the time it will take to accomplish it. The time will pass anyway.”

― Earl Nightingale

Like last time, an inelegant solution; but it doesn't use arrays or SQL or anything fancy.

In cell S2 (and down), if this is the close price, display the percentage difference between the opening price and the closing price:
=IFERROR(IF(ROW()=T2,INDIRECT("j"&T2)/INDIRECT("j"&U2),""),"")

In cell T2 (and down), we store the row number of the horse's opening price:
=IFERROR(IF(H2="","",IF(NOT(H2=H1),ROW(),T1)),"")

In cell U2 (and down), we store the row number of the horse's closing price:
=IF(T2=T3,U3,ROW())


So in cells S2 through to U2, it displays the following values:
1.509433962 2 23

Golden Sally

5.3 Cell:J23
8 CellJ2


=(J2-$J23)*100/$J23

50.94%


'I think

TO and Puntz, you're right in what you say, but the reason behind it is that when you have a steamer, the answer would be a negative %. This way anything under a 100% is a steamer and over a drifter. As long as the user is aware, but very observant and I'm glad you're paying attention in class," up the back there"...lol

I've added an example of a steamer to the spreadsheet.

Walkermac, thanks mate, you're a true artisan of the excel craft, and your champion crown remains intact, without challenge

Case closed, many thanks.

Attached Files

% Change Query.xlsx (17.3 KB, 842 views)