bad arithmetic

[Guest]

I have some VBA code within and Excel spreadsheet doing some simple
calculations, and then comparing the results. Although the numbers are
exactly the same, VBA thinks that they are different. Here is a
stripped-down version of the code.

First button to perform calculations:

Dim div_by_nine As Double

div_by_nine = Range("A1") / 9

Range("C1").Value = div_by_nine
Range("D1").Value = div_by_nine
Range("E1").Value = div_by_nine
Range("F1").Value = div_by_nine
Range("G1").Value = div_by_nine
Range("H1").Value = div_by_nine
Range("I1").Value = div_by_nine
Range("J1").Value = div_by_nine
Range("K1").Value = div_by_nine


Second button to compare results:

If Range("A1") <> Range("A3") Then
MsgBox ("Values don't match")
Else
MsgBox ("Values match")
End If
MsgBox ("orig value = " & Range("A1") & "; calculated value = " &
Range("A3"))

Also, add the following formula in cell A3 on the spreadsheet:
=SUM(C1:K1)

To test, type in a value in cell A1, and then click on the first button to
spread the values, and then the second button to compare the results of A1
with A3. One number in particular that doesn't work is 12.825. (I know that
some other numbers don't work because of rounding, and I can get around
that,...but there is no rounding involved when using 12.825).

 

[JE McGimpsey]

Take a look here:

http://cpearson.com/excel/rounding.htm


The problem *is* rounding, but it's inherent in the way IEEE double
precision floating point math is done (on all spreadsheets).