Rounding routine

[Raoul Watson]

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.

[Rick Rothstein]

Re: Rounding routine

> By any chance, anyone got a rounding routine that does a work around
the VB[color=blue]
> "round" bug?
> I still find it amazing that a company as large as Microsoft would put[/color]
out a[color=blue]
> math package that is so problematic.[/color]

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

[J French]

Re: Rounding routine

On Sat, 26 Jun 2004 04:15:15 -0400, "Rick Rothstein"
<rickNOSPAMnews @NOSPAMcomcast. net> wrote:
[color=blue][color=green]
>> By any chance, anyone got a rounding routine that does a work around[/color]
>the VB[color=green]
>> "round" bug?
>> I still find it amazing that a company as large as Microsoft would put[/color]
>out a[color=green]
>> math package that is so problematic.[/color]
>
>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,[/color]

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 )

[Raoul Watson]

Re: Rounding routine


"Rick Rothstein" <rickNOSPAMnews @NOSPAMcomcast. net> wrote in message
news:v-WdnXfNwKwRrUDdR Vn-jA@comcast.com. ..[color=blue][color=green]
> > By any chance, anyone got a rounding routine that does a work around[/color]
> the VB[color=green]
> > "round" bug?
> > I still find it amazing that a company as large as Microsoft would put[/color]
> out a[color=green]
> > math package that is so problematic.[/color]
>
> 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
>[/color]
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.

[Rick Rothstein]

Re: Rounding routine

> No I am not referring to banker's rounding, I am referring to VB
internal[color=blue]
> lousy floating point errors.
>
> VB's ailment in its math function is caused because there are errors[/color]
in the[color=blue]
> 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[/color]
decimal[color=blue]
> 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[/color]
error[color=blue]
> (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[/color]
it /[color=blue]
> share it with everyone else when done.[/color]

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.5049999999999 95

I repeat, this is a problem in any language dealing with floating point
numbers (unless it is using an "integerize d" 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.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q279/7/55.ASP&NoWebContent=1

(Complete) Tutorial to Understand IEEE Floating-Point Errors

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q42/9/80.ASP&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

[Steve Gerrard]

Re: Rounding routine


"Raoul Watson" <WatsonR@Intell igenCIA.com> wrote in message
news:0fhDc.7651 $Xn.3946@nwrdny 03.gnilink.net. ..[color=blue]
>
> "Rick Rothstein" <rickNOSPAMnews @NOSPAMcomcast. net> wrote in message
> news:v-WdnXfNwKwRrUDdR Vn-jA@comcast.com. ..[/color]
[color=blue]
> VB's ailment in its math function is caused because there are errors[/color]
in the[color=blue]
> 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[/color]
decimal[color=blue]
> points you should get 68.51.
>
> This is caused because the underlying code that computes:
>
> ? int(68.505 * 100 + .5)/100
>
> gives 68.5!
>[/color]

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

[Rick Rothstein]

Re: Rounding routine

> I have not used the round function, but I do a lot of this sort of[color=blue]
> 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[/color]
than[color=blue]
> 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[/color]

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

[Steve Gerrard]

Re: Rounding routine


"Rick Rothstein" <rickNOSPAMnews @NOSPAMcomcast. net> wrote in message
news:S-qdndvPKph5KUDdR Vn-vA@comcast.com. ..
[color=blue]
> CLng uses Banker's Rounding, so your function **can** make mistakes.[/color]
The[color=blue]
> 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
>[/color]

Assuming you consider Banker's Rounding a mistake... <g>

[Rick Rothstein]

Re: Rounding routine

> > CLng uses Banker's Rounding, so your function **can** make mistakes.[color=blue]
> The[color=green]
> > 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
> >[/color]
>
> Assuming you consider Banker's Rounding a mistake... <g>[/color]

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.aspx?scid=KB;EN-US;Q196652

Rick

[Steve Gerrard]

Re: Rounding routine


"Rick Rothstein" <rickNOSPAMnews @NOSPAMcomcast. net> wrote in message
news:S-qdndvPKph5KUDdR Vn-vA@comcast.com. ..[color=blue]
> CLng uses Banker's Rounding, so your function **can** make mistakes.[/color]
The[color=blue]
> 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
>[/color]

Even worse:
?MyRound(32.75, 2)
32.76

[Steve Gerrard]

Re: Rounding routine


"Rick Rothstein" <rickNOSPAMnews @NOSPAMcomcast. net> wrote in message
news:xfqdnXdiXM Q5J0DdRVn-tw@comcast.com. ..[color=blue]
>
> 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[/color]
NEVER[color=blue]
> used Banker's Rounding in ANY of our calculations. Even Microsoft is[/color]
not[color=blue]
> 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
>[/color]

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

[Raoul Watson]

Re: Rounding routine


"Rick Rothstein" <rickNOSPAMnews @NOSPAMcomcast. net> wrote in message
news:hMSdnQz_NO Z2MEDdRVn-sQ@comcast.com. ..
[color=blue]
> I repeat, this is a problem in any language dealing with floating point
> numbers (unless it is using an "integerize d" decimal system such when
> using VB's Currency data type). You can read more on this problem
> here....
>[/color]
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.
[color=blue]
> 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
>[/color]

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)

[Rick Rothstein]

Re: Rounding routine

> So I get this function now:[color=blue]
>
> 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[/color]

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

[Steve Gerrard]

Re: Rounding routine


"Rick Rothstein" <rickNOSPAMnews @NOSPAMcomcast. net> wrote in message
news:DK6dnaxN8t bmVUDd4p2dnA@co mcast.com...
[color=blue]
> 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
>[/color]

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

[Rick Rothstein]

Re: Rounding routine

> > yields 68.5 and not the expected 68.51. Personally, I'd stick with
the[color=blue][color=green]
> > Format function that I showed earlier... it seems to work around[/color][/color]
this[color=blue][color=green]
> > problem automatically.
> >
> > Rick - MVP
> >[/color]
>
> 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[/color]

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