% Change excel query
 

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.

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.

The answer will need to be in excel along the lines of MATCH (event) MAX and MIN. The data files are downloaded month at a time in excel.

Yeah sort of Wally, except not MAX and MIN: MATCH (event) LOOKUP (first row in subset odds value) divided by (last row in subset odds value)*100.

Maybe an Array?

Righto, it can be done with a single SQL query, but it would need to use the new FIRST_VALUE and LAST_VALUE SQL window functions.

eg:
http://www.postgresql.org/docs/9.3/...ial-window.html
or
http://sqlmag.com/sql-server-2012/h...unctions-part-1

I've never used them before, so I'd need to play around with it to get it to work, and I doubt whether MS query would support it, you'd need to import the data into a ridgy-didge SQL database.

If you can't get excel to do it, this is the way I'd go.

Call me dumb or call me dumber....lol but I don't even know what SQL is?
Better to stick with excel. Cheers for your ideas though jazzy :)