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.
Rounding so the stake is valid
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.ShamLal wrote: ↑Wed Jun 17, 2020 2:13 pmI 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.
[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
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.
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.Derek27 wrote: ↑Wed Jun 17, 2020 3:30 pmI'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.
[edit] - it will work with the function i just pasted - voila:
You do not have the required permissions to view the files attached to this post.
Last edited by jimibt on Wed Jun 17, 2020 3:37 pm, edited 1 time in total.
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 wrote: ↑Wed Jun 17, 2020 3:32 pmactually, 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.Derek27 wrote: ↑Wed Jun 17, 2020 3:30 pmI'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.
see edit aboveDerek27 wrote: ↑Wed Jun 17, 2020 3:34 pmIn 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 wrote: ↑Wed Jun 17, 2020 3:32 pmactually, 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.Derek27 wrote: ↑Wed Jun 17, 2020 3:30 pmI'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.