Floating Decimal Issue? - Rounded values not matching

[starl]

I have two cells I compare (via VBA). Both cells are in pivot tables.
Both values appear to match, down to the decimal of .295.
I've used a simple = formula and Excel says they match.
I used format cells to extend the decimal as far as it can and I do not see any extra numbers.

Via VBA, I Round them to 2 decimals. And that's where the problem starts.
One value rounds to .3
The other values rounds to .29

Now the values no longer match..
Why are the values changing when rounded?
The variables are declared as Double

thanks

 

[Rick Rothstein]

Yes, Format(value, "0.00") does make the values equal.

why do you say 0.30 is a variant string? where do you get 0.30?
I get 0.3 when rounding the cell value...

There are two versions of Format... one with just the word as I posted and one with a $ sign after the word... without the $ sign, Format returns a variant and the quoted pattern is a String value, so the Variant's sub-type is String.. with the $ sign, Format returns a pure String value. If you use 0.00 as the format pattern, the Format function returns 0.30 as its value (remember, it is returning a String). You can see this by executing these two lines of code...

N = 0.295
MsgBox Format(N, "0.00")

But if you put the output from Format in a numeric variable (or cell formatted as General or Number), it will resort to a real number and lose the trailing zero (VB's "evil type coercion at work).

 

[Jerry Sullivan]

Another possibility is that one of the numbers is fractionally smaller than 0.295 and the other is fractionally greater than 0.295.

Excel can't store exactly 0.295 as a binary number, since it would be an infinite repeating binary number, so whatever calculation you are doing to arrive at that number will require some minute approximation. The same calculation should arrive at the same approximation, but different calculations could arrive at one estimated value slightly above and one slightly below.

 

[Jerry Sullivan]

Thought I'd better pony up an example...

Excel Workbook
	A	B	C	D	E
1	x	y	Calc	Result of Calc	Rounded Result
2	0.195	0.1	x+y	0.295000000000000	0.30
3	100.295	100.0	x-y	0.295000000000002	0.30
4	200.295	200.0	x-y	0.294999999999987	0.29
Sheet

Similar can be seen using VBA (no variables were used for the benefit of Rory and Richard)

Sub FloatingPointPrecisionLimitation()
    '--the exact value of each calc is 0.295
    Debug.Print "Calc1: " & 0.195 + 0.1
    Debug.Print "Calc2: " & 100.295 - 100
    Debug.Print "Calc3: " & 200.295 - 200
    
    Debug.Print "Round Calc1: " & Round(0.195 + 0.1, 2)
    Debug.Print "Round Calc2: " & Round(100.295 - 100, 2)
    Debug.Print "Round Calc3: " & Round(200.295 - 200, 2)
End Sub

Output:
Test1: 0.295
Test2: 0.295000000000002
Test3: 0.294999999999987
Round Test1: 0.3
Round Test2: 0.3
Round Test3: 0.29

 

[RoryA]

I've used a simple = formula and Excel says they match.

That wouldn't be true with the examples you quote. That's why I was focusing on the variables rather than the cell values.

 

[Fazza]

Similar can be seen using VBA (no variables were used for the benefit of Rory and Richard)

No variables were harmed in the testing of this VBA.

I like that somehow

 

[starl]

Jerry, when I subtracted them per Rory's suggestion, there was a difference way off in the ether.

Rory - I did the simple = formula again with the cells on the sheet and the result is TRUE.

I don't know what's going on... or is it because of getpivotdata? which is what the simple formula resolves itself to?
When I look at the values in the cell (as in what's IN the cell, not what's formatted for me to see).. they match.

When I use VBA to see if the cells are the same - it says no..
???
How can a formula on a sheet say yes, but via vba, say no?

 

[shg]

VBA does an exact comparison of values. Excel does some cosmetic rounding, which sometimes gives inconsistent results, e.g., A1=C1 returns True, but VLOOKUP(C1, A1:B10, 2, 0) fails.

If the data are values rather than formulas, try doing text to columns.

 

[RoryA]

Also if a cell is formatted as currency the Value property returns a currency value with 4DP only which was why I asked about Value2 which returns a Double

 

[Rick Rothstein]

When I use VBA to see if the cells are the same - it says no..
???
How can a formula on a sheet say yes, but via vba, say no?

Here is one way where the different types of rounding used on worksheets and in VB influences the result. Put these values and formula in the indicated cells...

A1: 1.25
A2: 1.35
A3: 0.125
A4: =ROUND(A1,1)-ROUND(A2,1)-ROUND(A3,1)=-0.2

Cell A4 will display TRUE. Now, execute this code line (it is the equivalent of the formula in A4) in either a Sub or directly from the Immediate Window...

MsgBox Round(Range("A1"), 1) - Round(Range("A2"), 1) - Round(Range("A3"), 1) = -0.2

The MessageBox will display False. The culprit here is Banker's Rounding used in VB... 1.25 rounds to 1.2 and 1.35 rounds to 1.4 whereas on the worksheet 1.25 rounds to 1.3 and 1.35 rounds to 1.4 thus causing the different results. Actually, that appears to be only one factor contributing to the different results. Apparently, worksheet calculations are handled differently in some way than calculations in VB... I would have expected that if I replaced the VB Round function calls with Format function calls, that the result would be the same as that for cell A4's formula given that Format uses the same type of rounding that the worksheet ROUND function uses, but it is not. This code line display False just like the above MsgBox code line does, but apparently for a different reason...

MsgBox Format(Range("A1"), "0.0") - Format(Range("A2"), "0.0") - Format(Range("A3"), "0.0") = -0.2

As I said, I guess there is some subtle difference in the underlying calculation engine between the two worlds. I presume most of the time these differences are hidden from us, but when the values being compared/subtracted are close enough in value to each other, all bets appear to be off. That takes us back to the old advice that floating point values should never be tested for being equal to each other, rather the absolute value of their difference should be tested as being less than some "near zero" threshhold instead.

 

[starl]

the absolute value of their difference should be tested as being less than some "near zero" threshhold instead.

ok. i understand absolute value, I understand difference.. but how to do "near zero" ?

And yes, finally understand what you're getting at.. considering financial guys use Excel like crazy for stuff.. why hasn't the screaming gotten MS to consolidate the two?