P: n/a

By any chance, anyone got a rounding routine that does a work around the VB
"round" bug?
I still find it amazing that a company as large as Microsoft would put out a
math package that is so problematic.  
Share this Question
P: n/a

> By any chance, anyone got a rounding routine that does a work around
the VB "round" bug? I still find it amazing that a company as large as Microsoft would put
out a math package that is so problematic.
It's not a bug, just (in my opinion) a dumb decision on their part.
Anyway, use the Format function (without a format specifier) as it uses
"normal" rounding rather than the Banker's Rounding (which you are
referring to as a "bug"). For example,
Debug.Print Format(2.5)
Rick  MVP  
P: n/a

On Sat, 26 Jun 2004 04:15:15 0400, "Rick Rothstein"
<ri************@NOSPAMcomcast.net> wrote: By any chance, anyone got a rounding routine that does a work around the VB "round" bug? I still find it amazing that a company as large as Microsoft would put out a math package that is so problematic.
It's not a bug, just (in my opinion) a dumb decision on their part. Anyway, use the Format function (without a format specifier) as it uses "normal" rounding rather than the Banker's Rounding (which you are referring to as a "bug"). For example,
It is funny, I have done quite a lot of work for banks
 and none of them used 'Bankers Rounding'
The most important thing was to keep 'symetrical rounding'
eg: 2,000,000.5 and 2,000,000.5
both round to 2,000,001 regardless of sign
To accountants and bankers the minus sign just means 'Debit'
 one can Debit a Credit Account or Credit a Debit account
all that happens is that the +/ signs are reversed
The only really peculiar rules I ran into were OATs (French Treasury
Bonds) where one rounded the interest per bond to 3 dp before
multiplying by the number of bonds
.... and Jap bonds where the unit interest is truncated to 7dp before
multiplying.
( something to do with grotty calculators I suspect )  
P: n/a

"Rick Rothstein" <ri************@NOSPAMcomcast.net> wrote in message
news:v********************@comcast.com... By any chance, anyone got a rounding routine that does a work around the VB "round" bug? I still find it amazing that a company as large as Microsoft would put out a math package that is so problematic.
It's not a bug, just (in my opinion) a dumb decision on their part. Anyway, use the Format function (without a format specifier) as it uses "normal" rounding rather than the Banker's Rounding (which you are referring to as a "bug"). For example,
Debug.Print Format(2.5)
Rick  MVP
Thanks Rick..
No I am not referring to banker's rounding, I am referring to VB internal
lousy floating point errors.
VB's ailment in its math function is caused because there are errors in the
underlying code. For example, if you do
? round (68.505,2)
you get 68.5, which is incorrect; for if you round 68.505 to two decimal
points you should get 68.51.
This is caused because the underlying code that computes:
? int(68.505 * 100 + .5)/100
gives 68.5!
A more serious error can be seen here:
? int(68.505 * 1000 + 5)
68509 !!! (should have been 68510)
So the bigger the multiplication factor of a fraction, the larger the error
(the above error is a full ONE).
But that's OK, if no one have one ready, I'll write one and I'll post it /
share it with everyone else when done.  
P: n/a

> No I am not referring to banker's rounding, I am referring to VB
internal lousy floating point errors.
VB's ailment in its math function is caused because there are errors
in the underlying code. For example, if you do
? round (68.505,2)
you get 68.5, which is incorrect; for if you round 68.505 to two
decimal points you should get 68.51.
This is caused because the underlying code that computes:
? int(68.505 * 100 + .5)/100
gives 68.5!
A more serious error can be seen here:
? int(68.505 * 1000 + 5) 68509 !!! (should have been 68510)
So the bigger the multiplication factor of a fraction, the larger the
error (the above error is a full ONE).
But that's OK, if no one have one ready, I'll write one and I'll post
it / share it with everyone else when done.
The error you are describing should be endemic to all programming
languages. The problem is most floating point numbers in a decimal
system do not have **exact** representations in the underlying binary
number system that computers use. The number 68.505 does not have an
exact binary equivalent for a given number of bits (32 for VB), so any
language must approximate the value. Part of the problem is VB hides
this approximation from you. Although a number of type Double can
contain 16 digits, VB only shows you 15 of them (keeping the 16th one
hidden as a "guard" digit and using it for display rounding purposes).
You can see this by doing the following Print statement in the Immediate
window...
Print 68.505  68
You won't get the 0.505 that you expect; rather, VB will expose the
"guard" digit and print
0.504999999999995
I repeat, this is a problem in any language dealing with floating point
numbers (unless it is using an "integerized" decimal system such when
using VB's Currency data type). You can read more on this problem
here....
INFO: Visual Basic and Arithmetic Precision http://support.microsoft.com/default...NoWebContent=1
(Complete) Tutorial to Understand IEEE FloatingPoint Errors http://support.microsoft.com/default...NoWebContent=1
Anyway, you can still use the Format function to handle this problem.
For example (DP is not of decimal places to round to)...
DP = 2
Number = 68.505
Print Format(Number, "0." & String$(DP, "0"))
will print 68.51 as expected. Also
DP = 0
Number = 68.505 * 1000 + 5
Print Format(Number, "0." & String$(DP, "0"))
will print 68510 as expected.
Rick  MVP  
P: n/a

"Raoul Watson" <Wa*****@IntelligenCIA.com> wrote in message
news:0f****************@nwrdny03.gnilink.net... "Rick Rothstein" <ri************@NOSPAMcomcast.net> wrote in message news:v********************@comcast.com...
VB's ailment in its math function is caused because there are errors
in the underlying code. For example, if you do
? round (68.505,2)
you get 68.5, which is incorrect; for if you round 68.505 to two
decimal points you should get 68.51.
This is caused because the underlying code that computes:
? int(68.505 * 100 + .5)/100
gives 68.5!
I have not used the round function, but I do a lot of this sort of
rounding with floating point numbers. Most of the problem goes away if
you use CLng() instead of Int().
Int() truncates, and often floating point numbers are slightly less than
they seem, i.e. 6850.5 + .5 seems to us to be exactly 6851, but may
internally be 6850.9999999999, which truncates to 6850.
CLng(), on the other hand,round off the fractions:
?CLng(68.505 * 100 + .5)/100
68.51
So a function like this might work for you:
Public Function MyRound(ByVal Num As Double, ByVal Place As Long) As
Double
Dim X As Double
Dim Y As Double
If Place = 0 Then
MyRound = CLng(Num)
Else
X = 10 ^ Place
Y = Sgn(Num) / 2
MyRound = CLng(Num * X + Y) / X
End If
End Function  
P: n/a

> I have not used the round function, but I do a lot of this sort of rounding with floating point numbers. Most of the problem goes away if you use CLng() instead of Int().
Int() truncates, and often floating point numbers are slightly less
than they seem, i.e. 6850.5 + .5 seems to us to be exactly 6851, but may internally be 6850.9999999999, which truncates to 6850.
CLng(), on the other hand,round off the fractions: ?CLng(68.505 * 100 + .5)/100 68.51
CLng uses Banker's Rounding, so your function **can** make mistakes. The
easiest place to see this is with the following...
Print MyRound(68.5, 0)
which will print 68. On the other hand,
Print MyRound(69.5, 0)
will print 70.
Rick  MVP  
P: n/a

"Rick Rothstein" <ri************@NOSPAMcomcast.net> wrote in message
news:S********************@comcast.com... CLng uses Banker's Rounding, so your function **can** make mistakes.
The easiest place to see this is with the following...
Print MyRound(68.5, 0)
which will print 68. On the other hand,
Print MyRound(69.5, 0)
will print 70.
Rick  MVP
Assuming you consider Banker's Rounding a mistake... <g>  
P: n/a

> > CLng uses Banker's Rounding, so your function **can** make mistakes. The easiest place to see this is with the following...
Print MyRound(68.5, 0)
which will print 68. On the other hand,
Print MyRound(69.5, 0)
will print 70.
Rick  MVP
Assuming you consider Banker's Rounding a mistake... <g>
Oh, it's a mistake alright; no question in my mind on that.<g> I was a
practicing Civil Engineer for more than 32 years and, trust me, we NEVER
used Banker's Rounding in ANY of our calculations. Even Microsoft is not
entirely sure whether Banker's Rounding is a good thing to use in its
various programs or not... http://support.microsoft.com/default...;ENUS;Q196652
Rick  
P: n/a

"Rick Rothstein" <ri************@NOSPAMcomcast.net> wrote in message
news:S********************@comcast.com... CLng uses Banker's Rounding, so your function **can** make mistakes.
The easiest place to see this is with the following...
Print MyRound(68.5, 0)
which will print 68. On the other hand,
Print MyRound(69.5, 0)
will print 70.
Rick  MVP
Even worse:
?MyRound(32.75,2)
32.76  
P: n/a

"Rick Rothstein" <ri************@NOSPAMcomcast.net> wrote in message
news:xf********************@comcast.com... Oh, it's a mistake alright; no question in my mind on that.<g> I was a practicing Civil Engineer for more than 32 years and, trust me, we
NEVER used Banker's Rounding in ANY of our calculations. Even Microsoft is
not entirely sure whether Banker's Rounding is a good thing to use in its various programs or not...
http://support.microsoft.com/default...;ENUS;Q196652
Rick
That is a pretty good review of the subject.
So I get this function now:
Public Function MyRound(ByVal Num As Double, ByVal Place As Long) As
Double
Dim X As Double
X = 10 ^ Place
MyRound = Fix(Num * X + 0.5 * Sgn(Num)) / X
End Function  
P: n/a

"Rick Rothstein" <ri************@NOSPAMcomcast.net> wrote in message
news:hM********************@comcast.com... I repeat, this is a problem in any language dealing with floating point numbers (unless it is using an "integerized" decimal system such when using VB's Currency data type). You can read more on this problem here....
Oh.. I understand IEEE floating point issues.. my point is that MS, knowing
this, should have work this into the "round" function rather than using the
excuse "we all know that certain numbers don't have exact binary
presentation."
The round function makes the claim that "Returns a number rounded to a
specified number of decimal places" is NOT working as claimed. Because the
engineers are aware of IEEE floating point math issues, they should have
build a solution into the "round" function.
Because they fail to do this, is exactly the reason why we have to create
our own rounding function.
Anyway, you can still use the Format function to handle this problem. For example (DP is not of decimal places to round to)...
DP = 2 Number = 68.505 Print Format(Number, "0." & String$(DP, "0"))
will print 68.51 as expected. Also
DP = 0 Number = 68.505 * 1000 + 5 Print Format(Number, "0." & String$(DP, "0"))
will print 68510 as expected.
Rick  MVP
Thanks for your suggestion Rick. I did one using your suggestion and it
seems OK for money (I know it's not perfect for other math but my use now is
very specific to money):
N = num * 1000 + 5
N = Int(N) / 1000
(then simply truncate to the number of decimal needed)  
P: n/a

> So I get this function now: Public Function MyRound(ByVal Num As Double, ByVal Place As Long) As Double Dim X As Double
X = 10 ^ Place MyRound = Fix(Num * X + 0.5 * Sgn(Num)) / X
End Function
Unfortunately, that isn't perfect either. If I remember correctly, the
article I referred you to mentions the truncation problem using Int on
floating point numbers that approximated downward when moved into the
binary world. The original number from the beginning of this thread is
one such number. Using your new function
Print MyRound(68.505, 2)
yields 68.5 and not the expected 68.51. Personally, I'd stick with the
Format function that I showed earlier... it seems to work around this
problem automatically.
Rick  MVP  
P: n/a

"Rick Rothstein" <ri************@NOSPAMcomcast.net> wrote in message
news:DK********************@comcast.com... yields 68.5 and not the expected 68.51. Personally, I'd stick with the Format function that I showed earlier... it seems to work around this problem automatically.
Rick  MVP
One of the reasons I started toying with this is that the format
function didn't work as you described.
For
Debug.Print Format(2.5)
I get
2.5
And for Format(68.505 * 100)/100 I get
68.505
I guess you could use Format(68.505 * 100, "0")/100  
P: n/a

> > yields 68.5 and not the expected 68.51. Personally, I'd stick with
the Format function that I showed earlier... it seems to work around
this problem automatically.
Rick  MVP
One of the reasons I started toying with this is that the format function didn't work as you described.
For Debug.Print Format(2.5) I get 2.5
And for Format(68.505 * 100)/100 I get 68.505
I guess you could use Format(68.505 * 100, "0")/100
See my followup where I expanded (corrected) the description of the
Format command (by including a format specification)....
Debug.Print Format(2.5, "0")
Debug.Print Format(68.505, "0.00")
etc.
Rick  MVP  
P: n/a

"Rick Rothstein" <ri************@NOSPAMcomcast.net> wrote in message
news:l********************@comcast.com... See my followup where I expanded (corrected) the description of the Format command (by including a format specification)....
Debug.Print Format(2.5, "0")
Debug.Print Format(68.505, "0.00")
etc.
Rick  MVP
Whoops, missed that. For some reason, once I got to the links, I figured
it was the end of the post, and stopped scrolling...  
P: n/a

On Sat, 26 Jun 2004 11:34:09 0700, "Steve Gerrard"
<no*************@comcast.net> wrote:
<snip> So I get this function now:
Public Function MyRound(ByVal Num As Double, ByVal Place As Long) As Double Dim X As Double
X = 10 ^ Place MyRound = Fix(Num * X + 0.5 * Sgn(Num)) / X
End Function
Steve,
I use this:
Q# = 10# ^ DP
N# = Int(Abs(N#) * Q# + 0.500001) / Q# * Sgn(N#) ' Must do Abs  
P: n/a

"J French" <er*****@nowhere.com> wrote in message
news:40****************@news.btclick.com... On Sat, 26 Jun 2004 11:34:09 0700, "Steve Gerrard" <no*************@comcast.net> wrote:
Steve,
I use this:
Q# = 10# ^ DP N# = Int(Abs(N#) * Q# + 0.500001) / Q# * Sgn(N#) ' Must do Abs
Thanks, I was wondering if it was possible to do that, i.e. add a little
bit more than 0.5.
In actual programs, I have not used any rounding function, but instead
do the rounding at the point in code where its needed, using whatever
mechanism works for the situation.
It is most peculiar that Format(68.505, "0.00") uses an internal
rounding mechanism which evidently is unavailable otherwise, and
requires such tweaky code to reproduce. Rick's approach, converting a
number to a string and back to a number just to round it off, is equally
unsatisfying.  
P: n/a

On Sun, 27 Jun 2004 09:19:47 0700, "Steve Gerrard"
<no*************@comcast.net> wrote:
<snip> Thanks, I was wondering if it was possible to do that, i.e. add a little bit more than 0.5.
The ABS() is essential  I forgot about it once ...
In actual programs, I have not used any rounding function, but instead do the rounding at the point in code where its needed, using whatever mechanism works for the situation.
Yes, it is tricky, now wherever possible I use the Currency type
It is most peculiar that Format(68.505, "0.00") uses an internal rounding mechanism which evidently is unavailable otherwise, and requires such tweaky code to reproduce. Rick's approach, converting a number to a string and back to a number just to round it off, is equally unsatisfying.
Yes, it is odd
MS actually improved things in Basic7
 but they regressed in VB5+
 couple of us were shocked when we realized that
Once one of the guys I worked with, wrote a double to string routine
in ASM  it was actually more accurate than the Basic7 one
I am never sure who to blame for these problems, Microsoft or the
Institute of Electrical and Electronic Engineers  
P: 2

Geez louise... I'm reading this 12 months on because I just came across an issue with rounding in the "Format" function and I just had to post because of the amount of absolute Tripe in this thread. IIRC in about 8th grade I was taught the correct way to round numbers.
A simple example is this. Type the following in immediate mode in VB and see which you think is correct:
?2 * CDbl(format(3.1/2.0, "0.0")) 3.2
?2 * Math.round(3.1/2.0, 2) 3.1
The rounding method used by Math.Round is correct. It rounds toward zero if the last digit is 5 and the preceding digit is even. It rounds away from zero if the preceding digit is odd. Whereas the OP (and the Format function) would always round away from zero...
 
P: 2

Actually, bad example of the rounding functions:
From my real world example. If I have the value 1015.70, which the total amount for an item on an invoice. That amount has a tax rate of 5.5%  but it has to be fed into a financial system at 10%. So, to do this we multiply the amount by .55, and round it to 2 decimal places giving a value of 558.64. For the remainder (which gets sent Tax free) we multiply by 0.45 and round to 2 decimal places which gives us 457.07 with the Format function  meaning the total of the two values is now 1015.71. If we use the Math.round function then the second amount is 457.06 and the total is correct.
  This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 11037
 replies: 20
 date asked: Jul 17 '05
