Help
Please
I need some formulas for excel. I have been going through progressively in the attached spreadsheet. I have been trying to learn as I go. But when I try to put the textbook and video tuition in, I am failing.
I did for a good period of time lay the field to a liability and made a good profit until the profit became less and less.
Now I wish to revisit the system. But if I am going to do it, I want to (1) be able to enter the info into another excel data base with tote prices and the on course bookmaker prices.
Betfair is a little sneaky with their data. They put all the horse races together, they join the TAB# with the horse’s name and then shuffle them up. And they keep the win and place markets separate.
I need formulas not VBA because I can understand the formula and I need to be able to transfer the formulas with suitable adaption into the worksheet with the other data.
Attached is a sheet that was derived from Betfair final data files. This was put through another spreadsheet that was VBA. It is the sheet that I used to test the final data before. It removes the harness races and gives a result based on laying the field. The result was columns A-R and X & Y.
I then removed the special events, the blank spaces where unmatched (NT races) and the odd $1.01 outcome.
I then split col G to G & H, sorted numerically and ranked the odds. Then added field size.
Then got stuck.
In the attached spread I have put what equations I am trying to achieve.
Col U I want to be able to put a liability in for each rank up to 24 horses. Thus I can do the calculation with variable liabilities. IE $120 for 1st , $110 for 2nd and the rest at $100. Or $0 for 1st etc. I want to be able to test many combinations until I find some good ones that work. So Col U will be a reference match for each rank. This may need to be a table
Col V was the start of my pain. I need a formula that says “when Col S (ranking) = 1 then match col U $U$4 and when Col S =2 match $U$6 etc to rank 24.
Col W is the betfair lay amount liability/(odds-1)
Col x and Y I want to keep as reference to compare to.
Col Z will be the laying based on the col W
Col AA is the sum of each race of col Z with the result at the end of each race.
Col AB is the odds expressed as a percentage
Col AC is the market%
Col AD is the factor to convert the odds % to suit a 100% market.
Col AE is the odds% in a 100% market
Col AF previously entropy was touted as a metric testing the validity of the pricing of the race. This is only 2 columns from here so AF is col AE x Col AE
Col AG is sum of AF per race placed at the last horse of the race.
Thanks in advance.
I have explained my procedure so if anybody else wanted to follow they could from the betfeiar final data. They would have to manually remove the trots. And copy the columns as values from col Z and AA.

Attached Files

formula.xlsx (406.8 KB, 1099 views)

__________________
"God made man in his image". He then went to great lengths to ensure that each and every person was unique. God then pondered and said "I will call these human, as very few will rise from the herd to be called individual" http://www.propun.com.au/racing_for...ttons/quote.gif