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]

What does:

msgbox var1 - var2

return?

You could get that sort of difference if one variable were a Single and the other a Double - are you certain about the declarations?

 

[starl]

When subtracting the values, I get 5.82076609134674E-11

....

those values are way out there and meaningless. How do I get rid of them so they don't interfere with the rounding?? And why do I only see them when I subtract?

 

[RoryA]

Not sure without the code. If you do the same subtraction but referring to the cells directly instead of through the variables, do you get the same result or do you get 0? If you get 0 then there's an issue with the variables.

 

[starl]

Richard, how can you like his comment?? I had to read it twice to understand it

code is basically:

dim var1 as double, var2 as double
var1 = round(cell.value,2)
var2 = round(cell.value,2)

if var1 <> var2 then
'do some stuff
end if

Original subtraction was done using cell.value
Using the variables, I get... 1.00000000093132E-02

why isn't Round trimming the excess decimal values?

 

[RoryA]

Does it help if you use Value2 instead of Value? If not can I see the file?

 

[Rick Rothstein]

code is basically:

dim var1 as double, var2 as double
var1 = round(cell.value,2)
var2 = round(cell.value,2)

if var1 <> var2 then
'do some stuff
end if

Original subtraction was done using cell.value
Using the variables, I get... 1.00000000093132E-02

why isn't Round trimming the excess decimal values?

What were the numbers in the two cells? If one of the had a hand-entered value with 5 in the third decimal place with no digits following it, and the other was a value that calculated to what looked like the same number, then you could be running into VB's Banker's Rounding problem (numbers ending in 5 being rounded to the previous decimal position round to the even number). All rounding in VB uses Banker's Rounding except rounding done by the Format function (which uses what I call "normal" rounding... 5 always round up). Try replacing the two highlighted lines with these and see if the problem goes away...

var1 = Format(Cell1.Value, "0.00")
var2 = Format(Cell2.Value, "0.00")

 

[starl]

Will try with Value2. Sorry, can't send file, though I can desktop share.
Using Value2, they still round the same way.. .29 vs .3

Rick - both numbers come from pivot tables connected to quickbooks.
Using Format, they both show .3...
possible solution? But why, with a 0.00 format do i get .3 instead of .29??

 

[Rick Rothstein]

Will try with Value2. Sorry, can't send file, though I can desktop share.
Using Value2, they still round the same way.. .29 vs .3

Rick - both numbers come from pivot tables connected to quickbooks.
Using Format, they both show .3...
possible solution? But why, with a 0.00 format do i get .3 instead of .29??

I don't do anything with Pivot Tables, but I would guess the format in VB does not apply to the numbers in Excel worksheets, so 0.30 (a Variant with a subtype of String) is probably being delivered Excel which is probably then converting the String value to a real number before placing the value in the cell (real numbers do not retain trailing zeroes). The key question is, though, is does Format make the two values equal?

 

[starl]

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