Formula help

Example spreadsheets and comments on example spreadsheets.
Post Reply
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 pm

Hi all, hope everyone is incredibly well and enjoying this great British weather :D

Is anyone able to help me out with a formula/help...
Screenshot 2021-08-05 141302.png
I am trying to indicate the number of tics between BSP and IPMIN as shown in the column to the right.

Apologies if this has already been posted, and thanks in advance.

RR
You do not have the required permissions to view the files attached to this post.
rostov
Posts: 50
Joined: Mon Jul 02, 2012 3:45 pm

Post from 'spreadbetting'

viewtopic.php?p=158870#p158870
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 pm

rostov wrote:
Thu Aug 05, 2021 2:58 pm
Post from 'spreadbetting'

viewtopic.php?p=158870#p158870
Thanks for the post…

A bit over my head that one.. i am probably being presumptuous in thinking there is a simpler method.
Trader Pat
Posts: 4327
Joined: Tue Oct 25, 2016 12:50 pm

How I do something similiar is I have a column that lists all Betfair prices 1.01 - 100. I then use the MATCH function to find where in the list the two prices are and then in a destination cell I just subtract one number from the other which gives the distance between the two numbers which is the same as the number of ticks.

So in your example if you wanted to know the # of ticks between 18.00 and 11.50 assuming 18.00 was in cell H5 and 11.50 was in cell I5 and the Betfair prices were listed in say column Q

Pick a cell and enter the formula =MATCH(H5,Q:Q,0) would give you the position in the list of 36
In the cell below =MATCH(I5,Q:Q,0) would give 49
Then in the cell you want the answer to appear in subtract one cell from the other. In my example this would be =AF12-AF13 which gives -13 so 13 ticks.

The problem you'll run into is BSP numbers like 38.27 & 69.79 obviously won't be in the list so you'll have to round the BSP numbers and add formulas that round numbers to whatever the interval is, so numbers between 10 and 20 will have to be rounded to nearest .5, number between 30 and 50 to nearest 2 etc...

It's not as elegant as the previous post but might be easier to get your head around.
You do not have the required permissions to view the files attached to this post.
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 pm

Trader Pat wrote:
Thu Aug 05, 2021 3:16 pm
How I do something similiar is I have a column that lists all Betfair prices 1.01 - 100. I then use the MATCH function to find where in the list the two prices are and then in a destination cell I just subtract one number from the other which gives the distance between the two numbers which is the same as the number of ticks.

So in your example if you wanted to know the # of ticks between 18.00 and 11.50 assuming 18.00 was in cell H5 and 11.50 was in cell I5 and the Betfair prices were listed in say column Q

Pick a cell and enter the formula =MATCH(H5,Q:Q,0) would give you the position in the list of 36
In the cell below =MATCH(I5,Q:Q,0) would give 49
Then in the cell you want the answer to appear in subtract one cell from the other. In my example this would be =AF12-AF13 which gives -13 so 13 ticks.

The problem you'll run into is BSP numbers like 38.27 & 69.79 obviously won't be in the list so you'll have to round the BSP numbers and add formulas that round numbers to whatever the interval is, so numbers between 10 and 20 will have to be rounded to nearest .5, number between 30 and 50 to nearest 2 etc...

It's not as elegant as the previous post but might be easier to get your head around.
Superb, Thanks for the reply Pat!

Makes perfect sense.
Trader Pat
Posts: 4327
Joined: Tue Oct 25, 2016 12:50 pm

Realrocknrolla wrote:
Thu Aug 05, 2021 3:25 pm
Superb, Thanks for the reply Pat!

Makes perfect sense.
No problem 👍
rostov
Posts: 50
Joined: Mon Jul 02, 2012 3:45 pm

Realrocknrolla wrote:
Thu Aug 05, 2021 3:12 pm
rostov wrote:
Thu Aug 05, 2021 2:58 pm
Post from 'spreadbetting'

viewtopic.php?p=158870#p158870
Thanks for the post…

A bit over my head that one.. i am probably being presumptuous in thinking there is a simpler method.
If you put the code into a VBA module and use the formula:

=getTicks(K8,L8)

K8 is the BSP and L8 is the IPMIN

The result will be the tick difference.
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 pm

rostov wrote:
Thu Aug 05, 2021 4:30 pm
Realrocknrolla wrote:
Thu Aug 05, 2021 3:12 pm
rostov wrote:
Thu Aug 05, 2021 2:58 pm
Post from 'spreadbetting'

viewtopic.php?p=158870#p158870
Thanks for the post…

A bit over my head that one.. i am probably being presumptuous in thinking there is a simpler method.
If you put the code into a VBA module and use the formula:

=getTicks(K8,L8)

K8 is the BSP and L8 is the IPMIN

The result will be the tick difference.
Thanks mate. Will give it a play.
rostov
Posts: 50
Joined: Mon Jul 02, 2012 3:45 pm

rostov wrote:
Thu Aug 05, 2021 4:30 pm
Realrocknrolla wrote:
Thu Aug 05, 2021 3:12 pm
rostov wrote:
Thu Aug 05, 2021 2:58 pm
Post from 'spreadbetting'

viewtopic.php?p=158870#p158870
Thanks for the post…

A bit over my head that one.. i am probably being presumptuous in thinking there is a simpler method.
If you put the code into a VBA module and use the formula:

=getTicks(K8,L8)

K8 is the BSP and L8 is the IPMIN

The result will be the tick difference.
Use the formula

=getValidOdds(K8)
=getValidOdds(L8)

to adjust the BSP and IPMIN .

.
Trader Pat
Posts: 4327
Joined: Tue Oct 25, 2016 12:50 pm

rostov wrote:
Thu Aug 05, 2021 4:38 pm
Use the formula

=getValidOdds(K8)

to adjust the BSP and IPMIN .

I'll nick some of that code to clean up my stone age era analysis sheet!

Thanks Rostov
User avatar
firlandsfarm
Posts: 2688
Joined: Sat May 03, 2014 8:20 am

I do the same as Pat. :) Something else some may find useful is I like to remove all the detail in BSP and revert it back to valid ticks ...

=if(and(CellAddress>1,CellAddress<=1000),if(CellAddress<2,Round(CellAddress,2),if(CellAddress<3,Round((CellAddress)*50,0)/50,if(CellAddress<4,Round((CellAddress)*20,0)/20,if(CellAddress<6,Round((CellAddress)*10,0)/10,if(CellAddress<10,Round((CellAddress)*5,0)/5,if(CellAddress<20,Round((CellAddress)*2,0)/2,if(CellAddress<30,Round((CellAddress),0),if(CellAddress<50,Round((CellAddress)/2,0)*2,if(CellAddress<100,Round((CellAddress)/5,0)*5,if(CellAddress<=1000,Round((CellAddress)/10,0)*10,1000)))))))))),"ERROR")

You can include the actual "Cell Address" using Excel's Replace facility and having done it once any copying of the formula cell will change the target cell relatively.
Post Reply

Return to “Bet Angel - Example spreadsheets”