Little help with an excel formula?

[conorm]

Hi I'm trying just to put this formula into an excel programme if anyone can help, it's just so I can add one denominator on to the current price and need it to vary depending on what the odds are, as between different prices the odds on betfair move in different increments, so far I've got this but it is returning an error?

=IF(AND(D25>=1.01,D25<=1.99),D25+0.01,IF(AND(D25>=2,D25<=2.98),D25+0.02,IF(AND(D25>=3,D25<=3.95),D25+0.05,IF(AND(D25>=4,D25<=5.9),D25+0.1,IF(AND(D25>=6,D25<=9.8),D25+0.2,IF(AND(D25>=10,D25<=19.5),D25+0.5,IF(AND(D25>=20,D25<=29),D25+1,IF(And(D25>=30,D25<=48),D25+2,IF(AND(D25>=50,D25<=95),D25+5,IF(AND(D25>=100,D25<=1000),D25+10)

[conorm]

Here is the full formula as it doesn;t look like it's shown up on the preious post

=IF(AND(D25>=1.01,D25<=1.99),D25+0.01,IF
(AND(D25>=2,D25<=2.98),D25+0.02,IF
(AND(D25>=3,D25<=3.95),D25+0.05,IF
(AND(D25>=4,D25<=5.9),D25+0.1,IF
(AND(D25>=6,D25<=9.8),D25+0.2,IF
(AND(D25>=10,D25<=19.5),D25+0.5,
IF(AND(D25>=20,D25<=29),D25+1,
IF(And(D25>=30,D25<=48),D25+2,
IF(AND(D25>=50,D25<=95),D25+5,
IF(AND(D25>=100,D25<=1000),D25+10))))

[Dabbla]

The formula is probably to big for the cell.
This is how i get round it.

[spreadbetting]

Have you considered using VBA to add your ticks rather than a formula?

[pooma]

You should put all of the ticks and offsets you want into a separate worksheet in the spreadsheet you're using and then use VLOOKUP.

[gutuami]

This code was created by guy called GaryRussell. This was posted on a forum:

In the Excel macro editor create a new module and insert the following code.

You can use the plusTicks or minusTicks function.
eg. =plusTicks(H5,2) to add 2 ticks to the odds

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