Solved Rounding decimal places

[PaquettePaul]

My understanding is that Microsoft products use bankers rounding on decimal places; i.e., the decimal is only rounding upwards if the decimal being rounded is 6 or higher. I looked at rounding in Microsoft documents and found Math.Round(value, MidpointRounding.AwayFromZero, decimal places).

Has anyone used this before successfully or does anyone have a better solution to round upwards for decimal examples 0.50 to 0.59 so that they read 0.

 

[Pat Hartman]

If you want to round up from .5, add .5 to the number and truncate the decimal

 

[CJ_London]

bankers rounding rounds to the nearest even number (0 counts as even)- so 0.5 rounds to zero but 1.5 will round to 2 as will 2.5.

?round(2.5,0)
2
?round(1.5,0)
2
?round(0.5,0)
0

your question has limited examples - what do you want to round 1.59 to?

but to answer your question deduct 0.1

?round(2.59-0.1,0)
2
?round(1.59-0.1,0)
1
?round(0.59-0.1,0)
0

if you just want a different rule for rounding down to zero, deduct 0.09
?round(2.59-0.09,0)
2
?round(1.59-0.09,0)
2
?round(0.59-0.09,0)
0

 

[pbaldy]

Allen has some functions here including an explanation of bankers rounding:

Microsoft Access tips: Rounding numbers in Access

How to round numbers in Microsoft Access: to the nearest number, rounding up, rounding down, to the nearest 5 cents, rounding dates and times, bankers rounding, and handling floating point errors

allenbrowne.com

 

[The_Doc_Man]

The function you referenced is not part of the standard VBA library. You can't get there easily from here because of the library that holds it. (More on that library in a moment.) This is the list of valid VBA library functions.

Functions (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

The rounding function from the VBA standard library is ROUND( number, places) and definitely uses Banker's rounding (i.e. to nearest even number). The question is, what would you want to use? Because of the binary nature of numbers in Access, there is some merit to choosing the "Banker's Rounding" methods (having to do with the ugliness of odd number conversion in a binary scheme.) There can be such schemes as "always away from zero" or "always towards zero" and there are other schemes as well. Depending on what you wanted to use, you might need to implement a function for your specific style of rounding.

The rounding function you named in your question comes from the .NET library, I think.

Math.Round Method (System)

Rounds a value to the nearest integer or to the specified number of fractional digits.

docs.microsoft.com

There is supposedly a way to have Access call a .NET function but you have some work to do.

A Beginner’s Guide to calling a .NET Framework Library from Access

Introduction In an earlier blog article I described how to call a .NET Framework Library from Excel. I have subsequently received several requests for a similar article dealing with calling .NET fr…

richnewman.wordpress.com

The author of this article says his method only works on certain libraries, so if you want to go this route, you MIGHT want to search a bit more on how to run .NET routines from a non-.NET utility.

 

[isladogs]

I'm unclear what your requirements are from post #1 as you said

solution to round upwards for decimal examples 0.50 to 0.59 so that they read 0

That sounds like you want to round downwards!
If so, use the Int or Fix functions:

?Int(0.57)
0
?Int(1.32)
1
?Int(2.99)
2

Fix gives the same output for positive numbers
However, if you have negative numbers the results are different

?Int(-2.99)
-3
?Fix(-2.99)
-2

 

[arnelgp]

are you Round Up/Down by Whole number?
example:

1.59 when rounded will be 1?
1.6 will be rounded to 2?

'arnelgp
Public Function RoundUpOrDown(ByVal x As Double, Optional factor As Double = 0.59) As Double
Dim s As String, sdec As String
Dim wh As Long, ddec As Double
Dim i As Integer
RoundUpOrDown = x
If factor = 0 Then Exit Function
If factor > 1 Then Exit Function
s = x & "": i = InStr(1, s, ".")
If i = 0 Then Exit Function
sdec = Mid$(s, i): ddec = Val(Mid$(s, i))
s = Replace$(s, sdec, ""): wh = Val(s)
RoundUpOrDown = wh + (ddec <= factor) + 1
End Function

 

[PaquettePaul]

Searching for other information, I found this nugget that solves my rounding issue.

As previously stated, MS Access uses bankers rounding which causes numbers to be rounded towards the nearest even number. However, the Revenue Canada guidelines state that standard round up on .5 is the approach to be taken for calculating tax. The simple manner in which this can be accomplished is:
dbNum = Round((aNum1*aPct)+0.000001, 2)

The addition of the very small decimal is even to cause rounding upwards of x.5 but not enough to add a cent in other cases. when I used this, all of my tax calculations matched my brothers who was using a complicated approach in Excel.