Rounding Errors in VBA

[Danna Dowdy]

I have written a function that will first truncate a number, then round
it to a given number of significant digits. It works the majority of
the time but I am getting a few random errors.

For example:
..03305 results in 0.331 instead of 0.330
0.03575 results in 0.357 instead of 0.358
5.015 results in 5.01 instead of 5.02
..02235 results in .0223 instead of .0224
Out of 1400 results, these were the only that were incorrect...

Any ideas??

 

[Jim Rech]

This article explains how the VB round function works:

http://support.microsoft.com/default.aspx?scid=kb;en-us;225330&Product=xlw

 

[Danna Dowdy]

I have read this article and this is they way that I need to be rounding
but if you notice the numbers that I posted, they are not consistent
with the explanation of the rounding function in VB vs Excel.
As I said, most of them are rounding correctly based on this
explanation, but these particular results do not match what should be
happening.

 

[Jim Rech]

I see your point. Clearly that article and 194983 do not tell the whole
story. Maybe it has something to do with the imprecision of converting base
10 numbers to binary and back. Note though that the article talks only of
using 0 or no decimal places with the function.

?round(csng(0.03575),4)
0.0358

?round(cdbl(0.03575),4)
0.0357

 

[Jerry W. Lewis]

It is a binary thing. None of these numbers are exactly representable
in binary. Identifying whether the last decimal digit is > = or < 5 is
an iffy proposition in binary.

It gets worse when you calculate the numbers from formulas, since there
are several different binary numbers that would display as 5.015
regardless of the number of decimal places requested. The worksheet
ROUND() function seems to be better buffered against the vagaries of
binary approximations than the VBA Round() function. You can get some
improvement in VBA by using
Round(CDbl(CStr(x)),n)
instead of
Round(x,n)
but it will not correct all of these.

Jerry