By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 2,015 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Rounding routine

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.
Jul 17 '05 #1
Share this Question
Share on Google+
20 Replies


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

Jul 17 '05 #2

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 )

Jul 17 '05 #3

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.
Jul 17 '05 #4

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 Floating-Point 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

Jul 17 '05 #5

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

Jul 17 '05 #6

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

Jul 17 '05 #7

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>

Jul 17 '05 #8

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...;EN-US;Q196652

Rick

Jul 17 '05 #9

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
Jul 17 '05 #10

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...;EN-US;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

Jul 17 '05 #11

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)

Jul 17 '05 #12

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

Jul 17 '05 #13

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
Jul 17 '05 #14

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 follow-up 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

Jul 17 '05 #15

P: n/a

"Rick Rothstein" <ri************@NOSPAMcomcast.net> wrote in message
news:l-********************@comcast.com...
See my follow-up 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...
Jul 17 '05 #16

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
Jul 17 '05 #17

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.

Jul 17 '05 #18

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
Jul 17 '05 #19

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...
Jul 20 '06 #20

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.
Jul 20 '06 #21

This discussion thread is closed

Replies have been disabled for this discussion.