Solved

# Rounding differences between W2K and XP ???

Posted on 2002-06-12
569 Views
Hi

I have a big problem this morning.

I have an application that runs on multiple PCs (all using the same version of the application and all connected to the same database). One of them is running XP while all the others are running W2K.

On the XP system, I have some calculations that are totalling to 8863.27 (while it is giving me 8863.26 on all other PCs).

Anyone else found something like this? Are you aware of a setting somewhere in Windows that could affect this?

PS: I will install Windows XP this weekend to try it.
0
Question by:Éric Moreau
• 15
• 6
• 6
• +1

LVL 18

Expert Comment

ID: 7075660
emoreau,

I don't know why the rounding would be different from one computer to another, but to revisit VB's rounding methods...

you probably know that when working with calculations of numeric datatype variables, vb's implicit rounding uses "the computer's rule" (computer in this case is a person like an accountant, not a hardware computer), which is this weird rule that it will round down a digit that is 5 if the next significant digit is an even number, and round the digit up if the next significant digit is an odd number.  Here is the MSDN on it:

When you convert a decimal value to an integer value, Microsoft® Visual Basic® for Applications (VBA) rounds the number to an integer value. How it rounds depends on the value of the digit immediately to the right of the decimal place — digits less than 5 are rounded down, while digits greater than 5 are rounded up. If the digit is 5, then it is rounded down if the digit immediately to the left of the decimal place is even, and up if it is odd. When the digit to be rounded is a 5, the result is always an even integer.

For example, running the following line of code from the Immediate window prints "8," because VBA rounds down when the number immediately to the left of the decimal is even:

? CLng(8.5)
However, this code prints "10," because 9 is odd:

? CLng(9.5)

This can often give you results that you do not expect.  Normally, I expect that if I have a number:

33.125

and I want to round this to 2 decimal postions, then I would expect it to go to 33.13.  The only way that I can reliably do this is to use the Format statement, which will use the rouding rule that digits  equal to 5 round to the next higher number.  So, if I have a variable with 3 decimal positions and I want to round it to 2 decimal positions, I have to go through this painful process:

Dim x as single

x = 33.125

x = CSng(Format(x, "##.00")

This will turn x into 33.13 as I would expect.

My only guess for your problem is that under XP, perhaps, some intrinsic datatypes have different numbers of bytes associated with them than under W2K, and with these extra bytes, a number that might have been:

33.45676544556654322

Under W2K might look like this under XP

33.45676544556654322987665543235

If the combination of digits is just right, the XP number might round differently than the W2K.  This is a pretty wild guess, but you might want to examine each intermediate result in your calculation to see if the calculation is rounding the way that you would expect it to.  Then, if you can run the same test on an XP development machine, you might find that some intermediate result is rounding differently.  This is a painful process, as you have to check each part of a single calculation:

x = (((Y * Z) / Q) * 3.5)

Here, you'd have to check the result of Y * Z and then check the result of that result / Q and then check the result of that result * 3.5 etc.
0

LVL 69

Author Comment

ID: 7075725
Isn't a datatype be represented the same between all platforms? A VB integer is always -32767 to 32768!

I have installed XP late last night on a partition of my laptop and I will be able to test it tonight or tomorrow.
0

LVL 38

Expert Comment

ID: 7076096
Yes emoreau, you are right.  This is a serious bug if it is true.  Floating point operations should be strictly reproducible across all win32 platforms.
0

LVL 18

Expert Comment

ID: 7076404
my comment about datatypes was more about the underlying number of bytes used for the data type, and not the applicable values in the data type.  Under a 16-bit version of windows, an integer is only 2 bytes, but it's 4 bytes under a 32-bit OS.  My thinking, and it was only a wild speculation, was that if a datatype like Single had more bytes under XP than it did under W2K, perhaps the number would have greater precision.

One thing that might tell you if this Computer's Rule is to blame, would be to look at the value of the calculation before it is rounded to 2 digits.  If that third decimal postion was a 5 then you would see which rule is being used by which computers:

So if the original number is:
8863.265

Then computers displaying this are not using "the computer's rule"
8863.27

And computers displaying this are:
8863.26
0

LVL 38

Expert Comment

ID: 7076415
>my comment about datatypes was more about the underlying number of bytes used for the data type, and
not the applicable values in the data type.<

The size of the data type won't change over win32 platforms mdougan.  They are hardcoded in VB unlike in C.
0

LVL 18

Expert Comment

ID: 7076823
:) Hence the comment about "wild speculation"
0

LVL 69

Author Comment

ID: 7079214
I am finally up and running Windows XP. I have created a partition on my laptop (so one partition is running W2K and the other XP Pro).

I experience the same problem on my computer. So it seems to really be XP.

Try something as easy as the following line from the Immediate window:

? format(5511.49/2,"0.00")

Under W2K I get 2755.75
Under XP I get 2755.74

Can somebody else confirm it to me?

Is there anything I can change in the system settings to be sure I get the same results on both platforms?
0

LVL 69

Author Comment

ID: 7079256
I tried something else:

Private Sub Command2_Click()
Dim intI As Integer
Dim sngValue As Single

sngValue = 5511.4
For intI = 1 To 20
sngValue = Format(sngValue + 0.01, "0.00")
Debug.Print intI, sngValue, Format(sngValue / 2, "0.00")
Next intI
End Sub

Here are the results under XP (some results are the same 3 times and others are there only once):

1             5511.41      2755.70
2             5511.42      2755.71
3             5511.43      2755.72
4             5511.44      2755.72
5             5511.45      2755.72
6             5511.46      2755.73
7             5511.47      2755.74
8             5511.48      2755.74
9             5511.49      2755.74
10            5511.5       2755.75
11            5511.51      2755.76
12            5511.52      2755.76
13            5511.53      2755.76
14            5511.54      2755.77
15            5511.55      2755.78
16            5511.56      2755.78
17            5511.57      2755.78
18            5511.58      2755.79
19            5511.59      2755.80
20            5511.6       2755.80

Under W2K, I get (everything is there twice):

1             5511.41      2755.71
2             5511.42      2755.71
3             5511.43      2755.72
4             5511.44      2755.72
5             5511.45      2755.73
6             5511.46      2755.73
7             5511.47      2755.74
8             5511.48      2755.74
9             5511.49      2755.75
10            5511.5       2755.75
11            5511.51      2755.76
12            5511.52      2755.76
13            5511.53      2755.77
14            5511.54      2755.77
15            5511.55      2755.78
16            5511.56      2755.78
17            5511.57      2755.79
18            5511.58      2755.79
19            5511.59      2755.80
20            5511.6       2755.80

?????
0

LVL 38

Expert Comment

ID: 7079279
Third decimal *is* a five in this case, mdougan might have it there.

What does ?5511.49/2 give on both machines?
0

LVL 38

Expert Comment

ID: 7079281
Also does formatnumber(5511.49/2,2) give the same results on both machines?
0

LVL 69

Author Comment

ID: 7079298
--->?5511.49/2

W2K = 2755.745
XP  = 2755.745

--->formatnumber(5511.49/2,2)

W2K = 2,755.75
XP  = 2,755.74
0

LVL 75

Expert Comment

ID: 7079329
Aside from the fact that there are better ways of skinning this cat than using Single data type (I believe the last time I used Single was circa 1989, all the variables got replaced with Currency types).  So if we focus on the discrepancies between Windows 2000 and XP, what is evidently happening is that the Format is rounding as follows:
Windows 2000 >= 5 to the next value.
Windows XP is using Banker's rounding.

As to why this would have changed, who knows.

Anthony
0

LVL 69

Author Comment

ID: 7079333
Anthony,

What do you suggest to me to get uniform results?
0

LVL 69

Author Comment

ID: 7079337
>>all the variables got replaced with Currency types).

I changed the datatype from single to currency and I get the same results on both OS.
0

LVL 18

Expert Comment

ID: 7079356
Which result did you get with the currency data type?  The one using the Computer's Rounding Rule, or the Euro Rounding rule?
0

LVL 69

Author Comment

ID: 7079367
by same results, I meant that is still get the same results as of my post of "Date: 06/14/2002 01:09PM PST "
0

LVL 75

Expert Comment

ID: 7079438
Eric,

After posting my last message, with the implied suggestion of using Currency, I realized the workaround was more than likely useless as the problem seems to be in the Format not the data types.  Sorry about that red-herring!

Having said that, if you are looking for consistency (I know accuracy would be best) check out the Round function to see if it presents the same problems:

Dim intI As Integer
Dim sngValue As Currency

sngValue = 5511.4
For intI = 1 To 20
sngValue = sngValue + 0.01   ' No need for the Format
Debug.Print intI, sngValue, sngValue / 2, Format(sngValue / 2, "0.00"), Round(sngValue / 2, 2)
Next intI

Anthony
0

LVL 38

Expert Comment

ID: 7079484
The round function is supposed to use bankers rounding.  It should(!) be the same in both, but it will be different than the original format command that was used.
0

LVL 75

Expert Comment

ID: 7079500
Paul,

I realize that, but that was my point. At least this way the results should be consistent.

The bigger question is why the inconsistencies.

Anthony
0

LVL 75

Expert Comment

ID: 7079517
That was not very clear.  What I meant was that since on XP it appeared that Format was using Banker's rounding, while W2K was using Arithmetic Rounding, it would be interesting to know what happens with the Round function.  Perhaps some consistency (as accuracy is only in the eye of the beholder) could be achieved this way.

Anthony
0

LVL 69

Author Comment

ID: 7079921
>>it would be interesting to know what happens with
the Round function

Here it is!

The code:

Private Sub Command2_Click()
Dim intI As Integer
Dim sngValue As Single

sngValue = 5511.4
For intI = 1 To 20
sngValue = Format(sngValue + 0.01, "0.00")
Debug.Print intI, sngValue, Round(sngValue / 2, 2), Format(sngValue / 2, "0.00")
Next intI
End Sub

The result (on XP):

1             5511.41       2755.71      2755.70
2             5511.42       2755.71      2755.71
3             5511.43       2755.72      2755.72
4             5511.44       2755.72      2755.72
5             5511.45       2755.73      2755.72
6             5511.46       2755.73      2755.73
7             5511.47       2755.74      2755.74
8             5511.48       2755.74      2755.74
9             5511.49       2755.75      2755.74
10            5511.5        2755.75      2755.75
11            5511.51       2755.75      2755.76
12            5511.52       2755.76      2755.76
13            5511.53       2755.76      2755.76
14            5511.54       2755.77      2755.77
15            5511.55       2755.77      2755.78
16            5511.56       2755.78      2755.78
17            5511.57       2755.78      2755.78
18            5511.58       2755.79      2755.79
19            5511.59       2755.79      2755.80
20            5511.6        2755.8       2755.80

0

LVL 69

Author Comment

ID: 7079930
and here are the results on W2K:

1             5511.41       2755.71      2755.71
2             5511.42       2755.71      2755.71
3             5511.43       2755.72      2755.72
4             5511.44       2755.72      2755.72
5             5511.45       2755.73      2755.73
6             5511.46       2755.73      2755.73
7             5511.47       2755.74      2755.74
8             5511.48       2755.74      2755.74
9             5511.49       2755.75      2755.75
10            5511.5        2755.75      2755.75
11            5511.51       2755.75      2755.76
12            5511.52       2755.76      2755.76
13            5511.53       2755.76      2755.77
14            5511.54       2755.77      2755.77
15            5511.55       2755.77      2755.78
16            5511.56       2755.78      2755.78
17            5511.57       2755.78      2755.79
18            5511.58       2755.79      2755.79
19            5511.59       2755.79      2755.80
20            5511.6        2755.8       2755.80

The round column is equal on both OS but not the format column.

What should we conclude? What is the best way to prevent this? I need to display the same amount no matter which OS is running! I am pretty sure that you are (or will be) concerned too!
0

LVL 38

Accepted Solution

PaulHews earned 200 total points
ID: 7079986
Well it looks like the only problem is the Format function.  If you don't need compatibility with other versions and you don't mind the banker's rounding, you can use Round (putting the result into a format function) instead.  The other option would be to write your own format function.  That might be an option if the formatting you need is fairly basic.
0

LVL 75

Expert Comment

ID: 7080487
To add to what Paul mentioned you may want to take a look at the following article (although I suspect you have it memorized by now!):
HOWTO: Implement Custom Rounding Procedures (Q196652)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q196652&SD=MSKB&

Unrelated, but I still think you should use a Currency data type instead of Single.

Anthony
0

LVL 69

Author Comment

ID: 7080510
Yes I have seen this article. This is my next step. I think that really think that M\$ made a big mistake by changing the rounding method without any advertizing (someone saw it somewhere?).
0

LVL 69

Author Comment

ID: 7082713
I just got an idea but I was very disappointed!

When you open the properties of a file under XP, you can force it to be compatible to anoteher OS (win 95, 98, NT, 2000). Much like APCOMPAT.EXE under W2K. But it doesn't work.

I think the only thing I must do now is to write my own rounding procedure unless you help me find something else.
0

LVL 69

Author Comment

ID: 7082715
Somebody did a test like mine using VB and VFP. The results are strange (maybe it is not XP itself but a DLL!?!?!?):

I also make some tests:

W2K Advanced server, VB6 Professional SP5 - 2755.75
W2K Professional, VB6 Professional without SP - 2755.75
XP Professional, VB6 Enterprise SP5 - 2755.74

For Visual FoxPro with

SET DECIMALS TO 2

W2K Advanced server, VFP 7.0 without SP / VFP6.0 SP5 - 2755.75
W2K Professional, VFP 7.0 without SP / VFP 6.0 without SP - 2755.75
XP Professional, VFP7.0 SP1 / VFP 6.0 SP5 - 2755.75

First three computers are the same with second three respectively.
0

LVL 18

Expert Comment

ID: 7082758
emoreau,  again, it's just a wild speculation, but since you are runing so many tests, can you set the location of the XP computer to maybe US West Coast.  I am insterested to see if they have tried to get XP to round based on the localization settings.  If you are in Europe, then maybe the default rounding method is for Euro coversion....
0

LVL 69

Author Comment

ID: 7082760
I am up north of you. You know Canada. My regional settings are all sets to US.
0

LVL 18

Expert Comment

ID: 7083960
:)  OK, then never mind...
0

LVL 69

Author Comment

ID: 7084918
For interested people, here is my rounding routine which is working great so far:

' ************************************************************************************************
' *
' * My own rounding routing because rounding behavior is different from W2K to XP
' * Eric Moreau 2002.06.17
' *
' ************************************************************************************************
Public Function EMRound(ByVal pdblNumber As Double, _
Optional ByVal pintDigits As Integer = 2) As Currency
'? emround(1234.567189,-1)  = 1230
'? emround(5511.49/2,2)     = 2755.75
'? emround(1234.567189,4)   = 1234.5672

Dim lngNumber As Long
Dim sngFactor As Single
Dim sngModulo As Single

On Error GoTo ErrHandler

sngFactor = 10 ^ pintDigits
lngNumber = CLng(pdblNumber * sngFactor)
sngModulo = (pdblNumber * sngFactor) - lngNumber
If sngModulo >= 0.5 Then
lngNumber = lngNumber + 1
End If
EMRound = lngNumber / sngFactor

Exit Function
ErrHandler:
EMRound = 0
End Function
0

LVL 69

Author Comment

ID: 7090318
Thanks everyone for the input.

I have ended writting my own rounding routine which gives me accurate and consistent results.
0

## Featured Post

### Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…