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.
20  11671 
> 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
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 )
"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.
> 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
"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
> 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
"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>
> > 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
"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
"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
"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)
> 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
"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
> > 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
"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...
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
"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.
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
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...
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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: dhildebrandt |
last post by:
I have a query that uses the Round function to change decimals into
whole numbers. For 4 years straight the thing has always rounded the
numbers in exactly the same way so that whenever I update...
|
by: spebola |
last post by:
I am using vb.net 2003 professional and I get the following results
when using the round method:
dim Amount as decimal = 180.255
Amount = Amount.Round(Amount, 2)
Amount now contains 180.25. ...
|
by: Zorpiedoman |
last post by:
Howcome:
Dim D as decimal = .5D
msgbox d.Round(D, 0)
this returns "0"
Now when I went to school .5 rounds UP to 1 not DOWN to zero?????!!!
Documentation says this, but what the heck are...
|
by: cj |
last post by:
Lets assume all calculations are done with decimal data types so things
are as precise as possible.
When it comes to the final rounding to cut a check to pay dividends for
example in VB rounding...
|
by: AMDRIT |
last post by:
Given:
?System.Math.Round(161.5D,0)
162.0
?System.Math.Round(162.5D,0)
162.0
?System.Math.Round(163.5D,0)
164.0
In either C# '05 or VB.Net '05, can anyone explain the inconsistancy? Is
|
by: The Cool Giraffe |
last post by:
I have trouble making the following line compile.
int a = round (2.6);
I have, of course, included <cmathand i can go flooring and
ceiling with no problems. However, the rounding seems...
|
by: jdrott1 |
last post by:
i'm trying to round my currency string to end in 9. it's for a
pricing application.
this is the function i'm using to get the item in currency:
FormatCurrency(BoxCost, , , , TriState.True)
if...
|
by: md |
last post by:
Hi
Does any body know, how to round a double value with a specific number
of digits after the decimal points?
A function like this:
RoundMyDouble (double &value, short numberOfPrecisions)
...
|
by: jacob navia |
last post by:
Hi
"How can I round a number to x decimal places" ?
This question keeps appearing. I would propose the following
solution
#include <float.h>
#include <math.h>
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
