Formula help

[Realrocknrolla]

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

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

[rostov]

Post from 'spreadbetting'

viewtopic.php?p=158870#p158870

[Realrocknrolla]

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]

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.

[Realrocknrolla]

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]

Superb, Thanks for the reply Pat!

Makes perfect sense.

No problem

[rostov]

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.

[Realrocknrolla]

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]

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]

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

[firlandsfarm]

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.