Rounding so the stake is valid

[ShamLal]

I am pulling numbers from another sheet to automatically place bets. Sometimes the bets are placed because the numbers are valid selections (i.e. "2.00" or "6.60") however sometimes they are not because they are not valid (i.e. "11.20" or "27.20" and Status is FAILED). How do I round to the nearest acceptable odds value, or just round up, or just down?

I was also thinking I would like to exclude odds over a certain value, say 50 or 100.

[jimibt]

I am pulling numbers from another sheet to automatically place bets. Sometimes the bets are placed because the numbers are valid selections (i.e. "2.00" or "6.60") however sometimes they are not because they are not valid (i.e. "11.20" or "27.20" and Status is FAILED). How do I round to the nearest acceptable odds value, or just round up, or just down?

I was also thinking I would like to exclude odds over a certain value, say 50 or 100.

if you're using the bet angel vba sheet, you could add some functions that automatically look at your odds and either round up/down to the nearest valid value. I may have something that i modified from an upload by spreadbetting (a forum member). will have a look.

[edit] - this is part of the code that SB uploaded, tho i may have changed it slightly as this is pulled from a class that i created. it will round DOWN to the nearest valid odds. So, 11.6 would return 11.5, 11.2 would return 11.0 etc...

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

[ShamLal]

I am not using VBA

Also its more complicated than that as the increments change...

[Derek27]

I'm just thinking of the top of my head: you can use the IF function to test the range of the odds and have a table of increments. For instance, if the odds are between 3.0 and 3.95 the increment would be 0.05. Then divide by 0.05, take the integer and multiply by 0.05.

You can use the lookup or index functions to find the increment on the table.

[jimibt]

I'm just thinking of the top of my head: you can use the IF function to test the range of the odds and have a table of increments. For instance, if the odds are between 3.0 and 3.95 the increment would be 0.05. Then divide by 0.05, take the integer and multiply by 0.05.

You can use the lookup or index functions to find the increment on the table.

actually, you just made me realise something that i'd forgotten. you can reference vba functions from the excel sheet as if they are native functions. excel ain't really my thing but i rmbr doing it (for a very similar use case) a while back.

[edit] - it will work with the function i just pasted - voila:

[Derek27]

I'm just thinking of the top of my head: you can use the IF function to test the range of the odds and have a table of increments. For instance, if the odds are between 3.0 and 3.95 the increment would be 0.05. Then divide by 0.05, take the integer and multiply by 0.05.

You can use the lookup or index functions to find the increment on the table.

actually, you just made me realise something that i'd forgotten. you can reference vba functions from the excel sheet as if they are native functions. excel ain't really my thing but i rmbr doing it (for a very similar use case) a while back.

In Microsoft Access, bizarrely, the only way you can access a VBA variable from a query is to have a function and simply make it equal to the variable!

[jimibt]

I'm just thinking of the top of my head: you can use the IF function to test the range of the odds and have a table of increments. For instance, if the odds are between 3.0 and 3.95 the increment would be 0.05. Then divide by 0.05, take the integer and multiply by 0.05.

You can use the lookup or index functions to find the increment on the table.

actually, you just made me realise something that i'd forgotten. you can reference vba functions from the excel sheet as if they are native functions. excel ain't really my thing but i rmbr doing it (for a very similar use case) a while back.

In Microsoft Access, bizarrely, the only way you can access a VBA variable from a query is to have a function and simply make it equal to the variable!

see edit above