Rounding differences between W2K and XP ???

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.

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.

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.

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

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

:) Hence the comment about "wild speculation"

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?

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


?????

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

What does ?5511.49/2 give on both machines?

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

--->?5511.49/2

W2K = 2755.745
XP  = 2755.745


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

W2K = 2,755.75
XP  = 2,755.74

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

Anthony,

What do you suggest to me to get uniform results?

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

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

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

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

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.

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

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

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

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!

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

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?).

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.

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.

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

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

:)  OK, then never mind...

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

Thanks everyone for the input.

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