Rounding SP

[eduardoneco]

Hello everyone, I need some help with something that I can´t do it with excel, but it can be done with VBA (not sure, but I think it´s possible)....

I want to ount the ticks between two SP (ex. 3,05 and 2,96 = 5 ticks).

And if the SP of one number is 3,07 and the other 2,95??? I need an excel formula (not sure that exists) or a VBA code to round this numbers to the same odds as betfair as.

Can anyone help with this???

[doncht]

Same here. I've only seen this done at VBA but not through excel yet. Would be nice to know if this is possible and see the formula.

[Captain Sensible]

I'm guessing if you're calculating tick differences you're already using Gary's VBA tick routines and aware of how to use VBA. I wrote thisroutine to do exactly the same as you're trying to do.


i'e' =getValidOdds(Y14)

Just use it alongside the getTicks routine that's already on the site, obviously check it before using as you may want to roundup or down

Code: Select all

Function getValidOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.99
oddsInc = 0.02
Case 3 To 3.999
oddsInc = 0.05
Case 4 To 5.9999
oddsInc = 0.1
Case 6 To 9.9999
oddsInc = 0.2
Case 10 To 19.9999
oddsInc = 0.5
Case 20 To 29.99999
oddsInc = 1
Case 30 To 49.999
oddsInc = 2
Case 50 To 99.9999
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getValidOdds = Round(odds / oddsInc, 0) * oddsInc
Else
getValidOdds = 1000
End If
End Function

[Captain Sensible]

Here's the full code just needs to be placed in a module .


"I want to ount the ticks between two SP (ex. 3,05 and 2,96 = 5 ticks). "

You then call up any VBA routines like so

=getTicks(2.96,3.05)

the actual difference is 3 ticks not sure why you think it's 5.


"And if the SP of one number is 3,07 and the other 2,95??"

If your numbers aren't likely to be valid Betfair odds you can put them thru the getValidOdds routine first like so

=getTicks(getValidOdds(2.95),getValidOdds(3.07))

Code: Select all

Option Explicit

Function getValidOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.99
oddsInc = 0.02
Case 3 To 3.999
oddsInc = 0.05
Case 4 To 5.9999
oddsInc = 0.1
Case 6 To 9.9999
oddsInc = 0.2
Case 10 To 19.9999
oddsInc = 0.5
Case 20 To 29.99999
oddsInc = 1
Case 30 To 49.999
oddsInc = 2
Case 50 To 99.9999
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getValidOdds = Round(odds / oddsInc, 0) * oddsInc
Else
getValidOdds = 1000
End If
End Function



Function getPrevOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select
If Math.Round(odds - oddsInc, 2) >= 1.01 Then
getPrevOdds = Math.Round(odds - oddsInc, 2)
Else
getPrevOdds = 1.01
End If
End Function

Function getNextOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getNextOdds = Math.Round(odds + oddsInc, 2)
Else
getNextOdds = 1000
End If
End Function

Function plusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getNextOdds(odds)
Next
plusTicks = odds
End Function

Function minusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getPrevOdds(odds)
Next
minusTicks = odds
End Function




Function getOddsStepUp(ByVal odds As Currency) As Currency

Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
getOddsStepUp = oddsInc
End Function

Function getOddsStepDown(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select

getOddsStepDown = oddsInc
End Function



Function getTicks(odds1 As Currency, odds2 As Currency) As Single
Dim i As Double
Dim tickCount As Single
Dim thisStep As Double
Dim thisodds As Double

Select Case odds2
Case Is < 1.01, Is > 1000
GoTo Xit
End Select

Select Case odds1
Case Is < 1.01, Is > 1000
GoTo Xit

Case Is < odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepUp(i)
    i = i + thisStep
    tickCount = tickCount + 1
    Loop
 getTicks = tickCount
 
 Case Is > odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepDown(i)
    i = i - thisStep
    tickCount = tickCount + 1
    Loop
getTicks = tickCount - (tickCount * 2)

Case Is = odds2
getTicks = 0
End Select

Xit:
End Function

[Captain Sensible]

You can probably do things using excel formulas and absolute numbers etc but to be honest it's not worth the hassle when VBA is nice and simple

[eduardoneco]

Thank you Captain.....

The ticks that I mentioned (3,96 and 3,05) is really 3 not 5. Sorry......

As I said, I´m not to confortable with VBA code... The only code I have at this time is the time counter and it was copy from here....

The code that you wrote here I almost understand it (there are some things that I´m missing).... You know that with Excel formulas we wrote is always about the cells or a matrix. With that I´m ok....

But when I see some code like that you wrote, I don´t see the cells or matrix.

You can see in the file that I´m trying to wrote, that the only code that I have is the time counter.

Here´s the link:
http://www.4shared.com/file/fMdHG94pce/Horses.html

[Captain Sensible]

I can't download that file as it wants me to sign in with facebook etc

If you already have a sheet with VBA it's very simple, just follow the following steps

Open your excel sheet, then press Alt + F11 to open the VBA code editor.

Go to the menu at the top and select Insert->Module

In the module just copy and paste all the code below into the sheet, click the red x to close the editor, save the sheet and you're good to go, never need to look at the code again.

To call up those VBA routines you now just use excel the same as you'd use it for formulas, it's no harder than using IF

OK say we have our BSP odds in cell Y14 and we want to convert that to a valid Betfair SP we just call getValidOdds by entering

=getValidOdds(Y14)

into whatever cell we want it in

we want the tick difference between the back odds in cell F14 and the BSP odds in cell Y14 well just enter

=getTicks(F14,getValidOdds(Y14))

in the cell we want the result.

You also have access to commands like plusTicks and minusTicks if you want to add a certain amounts of ticks to a price

Code: Select all

Option Explicit

Function getValidOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.99
oddsInc = 0.02
Case 3 To 3.999
oddsInc = 0.05
Case 4 To 5.9999
oddsInc = 0.1
Case 6 To 9.9999
oddsInc = 0.2
Case 10 To 19.9999
oddsInc = 0.5
Case 20 To 29.99999
oddsInc = 1
Case 30 To 49.999
oddsInc = 2
Case 50 To 99.9999
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getValidOdds = Round(odds / oddsInc, 0) * oddsInc
Else
getValidOdds = 1000
End If
End Function



Function getPrevOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select
If Math.Round(odds - oddsInc, 2) >= 1.01 Then
getPrevOdds = Math.Round(odds - oddsInc, 2)
Else
getPrevOdds = 1.01
End If
End Function

Function getNextOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getNextOdds = Math.Round(odds + oddsInc, 2)
Else
getNextOdds = 1000
End If
End Function

Function plusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getNextOdds(odds)
Next
plusTicks = odds
End Function

Function minusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getPrevOdds(odds)
Next
minusTicks = odds
End Function




Function getOddsStepUp(ByVal odds As Currency) As Currency

Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
getOddsStepUp = oddsInc
End Function

Function getOddsStepDown(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select

getOddsStepDown = oddsInc
End Function



Function getTicks(odds1 As Currency, odds2 As Currency) As Single
Dim i As Double
Dim tickCount As Single
Dim thisStep As Double
Dim thisodds As Double

Select Case odds2
Case Is < 1.01, Is > 1000
GoTo Xit
End Select

Select Case odds1
Case Is < 1.01, Is > 1000
GoTo Xit

Case Is < odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepUp(i)
    i = i + thisStep
    tickCount = tickCount + 1
    Loop
 getTicks = tickCount
 
 Case Is > odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepDown(i)
    i = i - thisStep
    tickCount = tickCount + 1
    Loop
getTicks = tickCount - (tickCount * 2)

Case Is = odds2
getTicks = 0
End Select

Xit:
End Function

[eduardoneco]

DONE!!!! Many thanks again.....

Now I see more light at the end of the tunel of how this works......

Like the proberb that we use (Portugal): In a land of blindman´s, who have an eye is a King.....

Captain, again many thanks for your help.......

[Captain Sensible]

Actually it might best best to use this code below as I've altered it so it'll check the odds are valid before putting it thru the tick routines.

So you wouldn't need to put getValidOdds into the getTicks, plusTicks or minusTicks routines

so
=getTicks(getValidOdds(2.95),getValidOdds(3.07))

would simply become

=getTicks(2.95,3.07)

Code: Select all

Option Explicit

Function getValidOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.99
oddsInc = 0.02
Case 3 To 3.999
oddsInc = 0.05
Case 4 To 5.9999
oddsInc = 0.1
Case 6 To 9.9999
oddsInc = 0.2
Case 10 To 19.9999
oddsInc = 0.5
Case 20 To 29.99999
oddsInc = 1
Case 30 To 49.999
oddsInc = 2
Case 50 To 99.9999
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getValidOdds = Round(odds / oddsInc, 0) * oddsInc
Else
getValidOdds = 1000
End If
End Function



Function getPrevOdds(ByVal odds As Currency) As Currency

odds = getValidOdds(odds)
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select
If Math.Round(odds - oddsInc, 2) >= 1.01 Then
getPrevOdds = Math.Round(odds - oddsInc, 2)
Else
getPrevOdds = 1.01
End If
End Function

Function getNextOdds(ByVal odds As Currency) As Currency
odds = getValidOdds(odds)
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getNextOdds = Math.Round(odds + oddsInc, 2)
Else
getNextOdds = 1000
End If
End Function

Function plusTicks(odds As Currency, ticks As Byte) As Currency
odds = getValidOdds(odds)
Dim i As Byte
For i = 1 To ticks
odds = getNextOdds(odds)
Next
plusTicks = odds
End Function

Function minusTicks(odds As Currency, ticks As Byte) As Currency
odds = getValidOdds(odds)
Dim i As Byte
For i = 1 To ticks
odds = getPrevOdds(odds)
Next
minusTicks = odds
End Function




Function getOddsStepUp(ByVal odds As Currency) As Currency
odds = getValidOdds(odds)
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
getOddsStepUp = oddsInc
End Function

Function getOddsStepDown(ByVal odds As Currency) As Currency
odds = getValidOdds(odds)
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select

getOddsStepDown = oddsInc
End Function



Function getTicks(odds1 As Currency, odds2 As Currency) As Single
odds1 = getValidOdds(odds1)
odds2 = getValidOdds(odds2)
Dim i As Double
Dim tickCount As Single
Dim thisStep As Double
Dim thisodds As Double

Select Case odds2
Case Is < 1.01, Is > 1000
GoTo Xit
End Select

Select Case odds1
Case Is < 1.01, Is > 1000
GoTo Xit

Case Is < odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepUp(i)
    i = i + thisStep
    tickCount = tickCount + 1
    Loop
 getTicks = tickCount
 
 Case Is > odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepDown(i)
    i = i - thisStep
    tickCount = tickCount + 1
    Loop
getTicks = tickCount - (tickCount * 2)

Case Is = odds2
getTicks = 0
End Select

Xit:
End Function


[eduardoneco]

Actually it might best best to use this code below as I've altered it so it'll check the odds are valid before putting it thru the tick routines.

So you wouldn't need to put getValidOdds into the getTicks, plusTicks or minusTicks routines

so
=getTicks(getValidOdds(2.95),getValidOdds(3.07))

would simply become

=getTicks(2.95,3.07)

Code: Select all

Option Explicit

Function getValidOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.99
oddsInc = 0.02
Case 3 To 3.999
oddsInc = 0.05
Case 4 To 5.9999
oddsInc = 0.1
Case 6 To 9.9999
oddsInc = 0.2
Case 10 To 19.9999
oddsInc = 0.5
Case 20 To 29.99999
oddsInc = 1
Case 30 To 49.999
oddsInc = 2
Case 50 To 99.9999
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getValidOdds = Round(odds / oddsInc, 0) * oddsInc
Else
getValidOdds = 1000
End If
End Function



Function getPrevOdds(ByVal odds As Currency) As Currency

odds = getValidOdds(odds)
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select
If Math.Round(odds - oddsInc, 2) >= 1.01 Then
getPrevOdds = Math.Round(odds - oddsInc, 2)
Else
getPrevOdds = 1.01
End If
End Function

Function getNextOdds(ByVal odds As Currency) As Currency
odds = getValidOdds(odds)
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getNextOdds = Math.Round(odds + oddsInc, 2)
Else
getNextOdds = 1000
End If
End Function

Function plusTicks(odds As Currency, ticks As Byte) As Currency
odds = getValidOdds(odds)
Dim i As Byte
For i = 1 To ticks
odds = getNextOdds(odds)
Next
plusTicks = odds
End Function

Function minusTicks(odds As Currency, ticks As Byte) As Currency
odds = getValidOdds(odds)
Dim i As Byte
For i = 1 To ticks
odds = getPrevOdds(odds)
Next
minusTicks = odds
End Function




Function getOddsStepUp(ByVal odds As Currency) As Currency
odds = getValidOdds(odds)
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
getOddsStepUp = oddsInc
End Function

Function getOddsStepDown(ByVal odds As Currency) As Currency
odds = getValidOdds(odds)
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select

getOddsStepDown = oddsInc
End Function



Function getTicks(odds1 As Currency, odds2 As Currency) As Single
odds1 = getValidOdds(odds1)
odds2 = getValidOdds(odds2)
Dim i As Double
Dim tickCount As Single
Dim thisStep As Double
Dim thisodds As Double

Select Case odds2
Case Is < 1.01, Is > 1000
GoTo Xit
End Select

Select Case odds1
Case Is < 1.01, Is > 1000
GoTo Xit

Case Is < odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepUp(i)
    i = i + thisStep
    tickCount = tickCount + 1
    Loop
 getTicks = tickCount
 
 Case Is > odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepDown(i)
    i = i - thisStep
    tickCount = tickCount + 1
    Loop
getTicks = tickCount - (tickCount * 2)

Case Is = odds2
getTicks = 0
End Select

Xit:
End Function


I put like you say: =getTicks(getValidOdds(Y2);getValidOdds(Z2))
and works fine. But I´ll change it to this one.

I want to make a repair (if you allow it)....

=getTicks(2.95 ; 3.07)

In my excel don´t work with "," but work with ";" (I don´t know if it´s the different settings we have, maybe it is)......

[Captain Sensible]

Yep it'll just be down to your setting using ; instead of , . Do as you wish with the code, most of it is stuff Gary posted I just tweaked it so it'd catch any errors that weren't valid Betfair increments.