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

 

[RoryA]

You could use something like

if round(abs(var1 - var2), 6) = 0 then

I agree there should be a new operator:

If var1 NearEnough var2 then

 

[Rick Rothstein]

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

You need to pick a "closeness" value that if the difference is less than that amount, you would be willing to consider them being equal. For example, something like this...

If Abs(Value2 - Value1) < 0.000001 Then
  MsgBox "Close enough for what I am doing, I'll consider them as being equal"
Else
  MsgBox "The values are not close enough to each other, I'll consider them different"
End If

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?

I was a Civil Engineer before my retirement, so I have never understood "Banker's Rounding"... it seems so unnatural to me, but I guess if you are in the financial end of things, it must make some kind of sense. All I know is that I was so thankful when I discovered (oh so many years ago now) that Format did "normal" rounding.

 

[Jon von der Heyden]

but I guess if you are in the financial end of things, it must make some kind of sense.

Accountants love to overcomplicate things! Speaking from [waaaay too much] experience.

Blo0dy awesome thread!!! Thanks guys.

 

[RoryA]

You can of course also use Application.Round rather than VBA.Round to get 'normal' rounding.

 

[starl]

You can of course also use Application.Round rather than VBA.Round to get 'normal' rounding.

oooh. That does work.

 

[Rick Rothstein]

oooh. That does work.

It appears to work the same way that Format does... with my set up from Message #19, this reports False...

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

the same as this does...

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

So there is still seems to be some kind of subtle difference in the underlying calculation engines between the two worlds.

 

[RoryA]

Application.round basically calls the ROUND worksheet function

 

[Rick Rothstein]

Application.round basically calls the ROUND worksheet function

I know that... and it performs a "normal" type of rounding (not the Banker's Rounding variety). VB's built-in Format function also performs a "normal" type of rounding (see my comment in Message #7) and produces the same rounded values as Application.Round does. The point of my last message harks back to what I posted in Message #19 where I demonstrated that the calculations performed on the worksheet can produce a different result than the equivalently identical code line does when executed in VB. From that, I had concluded (in Message #19) that there is some kind of subtle difference between the way Excel calculates expressions and the way VB calculates them. Now, my last comment was in response to Tracy's comment of "oooh. That does work." when replying to your suggestion to use Application.Round... all I was doing was making sure Tracy understood that Application.Round and Format produce the same results and that using Application.Round did not eliminate the problem with the subtle difference between how Excel and VB calculate equivalently identical expressions that I pointed out in Message #19.