Trying to round down to nearest .05 or .10 (1 Viewer)

moto485 · Apr 23, 2014

Hi I am increasing prices and after increasing I am left with 4 decimal places and am trying to figure out how to run an UPDATE query to round down prices to the nearest 5 cents, examples below:

42.4516 round to $42.45
42.4659 round to $42.45
42.4489 round to $42.40
2.49 round to $2.45
2.46 round to $2.45
2.44 round to $2.40
2.04 round to $2

I am using "Currency" format. I have been scouring the net but have come up empty handed and was hoping someone new a SQL formula for this. Thanks.

pr2-eugin · Apr 23, 2014

You might need a function to do this for you, this might not be the best, but surely does work ! Let me know how it goes.

Code:

Public Function RoundDown(numVar As Double) As Double
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    Dim intPart As Long, decPart As Double, tmpVar As String
    
    intPart = CLng(numVar)
    decPart = numVar - intPart
    
    If decPart > 0 Then
        tmpVar = Left(decPart, 4)
        tmpVar = Left(tmpVar, 3) & IIf(CLng(Right(tmpVar, 1)) < 5, 0, 5)
        RoundDown = CDbl(intPart + CDbl(tmpVar))
    Else
        RoundDown = CDbl(numVar)
    End If
End Function

Tested against your data !

Code:

? RoundDown(42.4516)
 42.45 

? RoundDown(42.4659)
 42.45 

? RoundDown(42.4489)
 42.4 

? RoundDown(2.49)
 2.45 

? RoundDown(2.46)
 2.45 

? RoundDown(2.44)
 2.4 

? RoundDown(2.04)
 2

MarkK · Apr 23, 2014

To round any number A to the nearest X

divide A by X
round the result of 1. to the nearest one - and here, to round down, you use the Int() function, which just drops the decimal. Other useful functions in this context in VBA: Fix(), CInt(), CLng(), Round()
multiply the result of 2. by X

So, yeah, depending what you do at step 2 you can always round up or down, or just do the usual.

pr2-eugin · Apr 23, 2014

MarkK said:
To round any number A to the nearest X

divide A by X

round the result of 1. to the nearest one - and here, to round down, you use the Int() function, which just drops the decimal. Other useful functions in this context in VBA: Fix(), CInt(), CLng(), Round()

multiply the result of 2. by X

So, yeah, depending what you do at step 2 you can always round up or down, or just do the usual.

This is a neat method ! Thanks MarkK !

moto485 · Apr 23, 2014

pr2-eugin said:

You might need a function to do this for you, this might not be the best, but surely does work ! Let me know how it goes.

Code:

Public Function RoundDown(numVar As Double) As Double
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    Dim intPart As Long, decPart As Double, tmpVar As String
    
    intPart = CLng(numVar)
    decPart = numVar - intPart
    
    If decPart > 0 Then
        tmpVar = Left(decPart, 4)
        tmpVar = Left(tmpVar, 3) & IIf(CLng(Right(tmpVar, 1)) < 5, 0, 5)
        RoundDown = CDbl(intPart + CDbl(tmpVar))
    Else
        RoundDown = CDbl(numVar)
    End If
End Function

Tested against your data !

Code:

? RoundDown(42.4516)
 42.45 

? RoundDown(42.4659)
 42.45 

? RoundDown(42.4489)
 42.4 

? RoundDown(2.49)
 2.45 

? RoundDown(2.46)
 2.45 

? RoundDown(2.44)
 2.4 

? RoundDown(2.04)
 2

Thanks for this code it works but for some reason didn't work for numbers like 1.9872 and 2.6784 and 7.8732

pr2-eugin · Apr 23, 2014

Scratch my method, follow MarkK's it is simpler and much smooth.

Code:

Public Function RoundDown(numVar As Double) As Double
[COLOR=Green]'*****************************************************************
'Code Courtesy of
'  MarkK
'Edited by
'  Paul Eugin
'Steps involved:
'   #1 - The input number, divided by the nearest 5 cent (0.05)
'   #2 - Use the Double number obtained in #1 and convert it to an Integer
'   #3 - Multiply the result obtained in #2 with 0.05
'*****************************************************************[/COLOR]
    RoundDown = Int(numVar / 0.05) * 0.05
End Function

MarkK · Apr 23, 2014

Watch out for Fix(). It rounds towards zero, so it rounds negative numbers UP, positive number DOWN.

pr2-eugin · Apr 23, 2014

MarkK said:
Watch out for Fix(). It rounds towards zero, so it rounds negative numbers UP, positive number DOWN.

Using CInt or CLng Rounds UP the value, leading to a not stable result. Int seems to work though !

moto485 · Apr 23, 2014

Amazing! Just going through a test run of a few thousand and looks to be perfect! You guys are wizards! Thanks

pr2-eugin · Apr 23, 2014

All credit goes to MarkK. I am simply elaborating the method he has shown !

However you are welcome !

We are glad to help ! Good luck !

MarkK · Apr 23, 2014

Team work.

tssparky · Jan 8, 2019

Thanks guys for the help, Particularly MarKK.
Using MarKK's Method I use this in our system.
I used this in a query to round down to nearest .05 being in a Currency Format.
Works well enough if you don't mind loosing up to .04 of a cent every transaction.

Code:

Price:FormatCurrency(Round(([RRP]/.05),0)*(.05))

Or This Works as well

Code:

Price:FormatCurrency(Round(20*[RRP],0)/20)

Works For Me

Trying to round down to nearest .05 or .10 (1 Viewer)

moto485

Registered User.

pr2-eugin

Super Moderator

MarkK

bit cruncher

pr2-eugin

Super Moderator

moto485

Registered User.

pr2-eugin

Super Moderator

MarkK

bit cruncher

pr2-eugin

Super Moderator

moto485

Registered User.

pr2-eugin

Super Moderator

MarkK

bit cruncher

tssparky

Registered User.

Similar threads

Users who are viewing this thread