Round Error on certain numbers only.

Question

I have noticed that the Round function seem to be randomly rounding up or down 0.005 depending on the leading decimal.  The issue showed up in a report, and I could confirm the issue by using Debug.Print Round(0.005,2) in the immediate Window. Here are the results from my test.  How long has this error been there?

Round(0.005,2) = 0.00 

Round(0.015,2) = 0.02

Round(0.025,2) = 0.02

Round(0.035,2) = 0.04

Round(0.045,2) = 0.04

Round(0.055,2) = 0.06

Round(0.065,2) = 0.06

Round(0.075,2) = 0.08

Round(0.085,2) = 0.08

Round(0.095,2) = 0.10

---

Mor10

Answer 1

I just check in Excel, and Excel does the calculations correctly.

---

Mor10

Answer 2

I have noticed that the Round function seem to be randomly rounding up or down 0.005 depending on the leading decimal.

Hi Mor10,

See the Round in Access:

Definition and Usage

The Round() function rounds a number to a specified number of decimal places.

Note: If the expression ends with a 5, this function rounds so that the last digit is an even number. Here are some examples:

Round(34.55, 1) - Result: 34.6 (rounds up)
Round(34.65, 1) - Result: 34.6 (rounds down)

Imb.

Answer 3

So it is on purpose. Why do they do it wrong on purpose?

---

Mor10

Answer 4

This is by design. Access uses the IEEE-754 method (also used by VBA) of rounding half to the nearest even digit: so-called banker's rounding.

See for example Rounding in Access

This article contains a link to a custom Round function that behaves like Excel's ROUND function.

---

Regards, Hans Vogelaar (https://www.eileenslounge.com)

Answer 5

You might like to take a look at the following link:

https://www.eetimes.com/an-introduction-to-different-rounding-algorithms/#

This outlines the various rounding algorithms which might be used in different contexts.

---

Ken Sheridan, Stafford, England

Answer 6

If you do an online search you can find functions that do rounding the proper way, then you call that function instead of the built-in one and life is good again.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 7

So it is on purpose. Why do they do it wrong on purpose?

---

Mor10

Hi Mor10,

It is not a matter of doing things wrong. You just want it to do in a different way, and there are many ways to do that.

Imb.

Answer 8

As already stated, what you see is the result of Banker's Rounding.

If you need normal 4/5 rounding (away from zero), the only native function in VBA offering this is Format.

If you require ultimate precision in rounding, use the functions found in my project VBA.Round.

For your current need, RoundMid is the function to apply.

---

Gustav Brock