Rounding up/down

[waldo]

I have built a spreadsheet which generates prices for me to lay/back at, unfortunately sometimes these figures do not fit into the Betfair betting increments and they are rejected. Is there any way round this as the betting increments vary so much that rounding up/down still doesnt work.

Any help greatly appreciated.

[nigelk]

It's a fairly long winded formula that you need. Your calculated figures, are they a percentage or decimal number?

[nicrag9]

I found that the best way for me was to write a "SELECT CASE" section of VBA. ie
SELECT CASE price
CASE IS <=1.96
newprice=
etc you have to work out the clicks and then program those figures

you can also do it with an IF THEN section but that tends to be less easy to read later

Nic

[chrisw]

hi Waldo
hope this helps. if you have a figure of say 2.37 in cell D70 then in your chosen cell would be this formula. =ROUNDUP(D70,1) this will give a figure of 2.4. =ROUNDDOWN WOULD GIVE 2.3
Cheers Chris

[Euler]

Why not create a table and use VLOOKUP to hunt for the values?

[TheTub]

I was about to say the same thing.

See the attached sheet that lists all the prices available in Betfair.

Betfair Price Increments.xls

[Ethanol]

You can use this VBA function which I wrote:

Code: Select all

Option Explicit

Function BetfairOdds(dOdds As Double, Optional iRoundType As Integer = 0)
  Dim dRound As Double
  Dim dReturnValue As Double

  Select Case dOdds
    Case Is <= 1.01
      dReturnValue = 1.01
    
    Case Is >= 1000
      dReturnValue = 1000
    
    Case Else
      'DETERMINE ROUNDING AMOUNT
      Select Case dOdds
        Case Is < 2
          dRound = 0.01
        Case Is < 3
          dRound = 0.02
        Case Is < 4
          dRound = 0.05
        Case Is < 6
          dRound = 0.1
        Case Is < 10
          dRound = 0.2
        Case Is < 20
          dRound = 0.5
        Case Is < 30
          dRound = 1
        Case Is < 50
          dRound = 2
        Case Is < 100
          dRound = 5
        Case Else
          dRound = 10
      End Select
      
      'APPLY THE APPROPRIATE ROUND TYPE TO THE ODDS
      Select Case iRoundType
        'DEFAULT ROUNDING
        Case 0
          dReturnValue = round(dOdds / dRound, 0) * dRound
  
        'ROUND UP
        Case 1
          dReturnValue = WorksheetFunction.Ceiling(dOdds / dRound, 1) * dRound
        
        'ROUND DOWN
        Case -1
          dReturnValue = WorksheetFunction.Floor(dOdds / dRound, 1) * dRound
          
        Case Else
          dReturnValue = 0
          
      End Select
  
  End Select

  BetfairOdds = dReturnValue
End Function

In the first parameter, pass the odds which you're checking.

The second parameter is used to choose whether to round up or down, as an alternative to using the default rounding (nearest value). Pass 1, 0, or -1 to round up, use the default, or round down accordingly. Alternatively, omit this parameter to use default rounding.

Example:

Code: Select all

BetfairOdds(3.02)     'RETURNS 3
BetfairOdds(3.02, 1)  'RETURNS 3.05
BetfairOdds(3.02, -1) 'RETURNS 3

Hope this helps.
__________

EDIT: Just noticed that some values returned a load of unnecessary zeroes after the decimal point, so I've fixed this by replacing the singles with doubles.

[waldo]

Thanks for all the help.

Numbers are in decimal form.

Not very knowledgable on VBA but will have a play around

[nigelk]

Assuming you've generated a non-existent price, 2.73 for example, the attached will give the higher and lower options ( 2.74 and 2.72 ).

If you've generated a valid price, both formulas will reflect that.

D216 is the generated non valid price, and the higher option is in F216, the lower in F217.

[thewald]

Thanks for all advice

@ethanol thanks, a little too complicated for me though

@nigelk perfect, thanks