Help Needed: Calculation error

[Adrian]

When I run the following code:

Public Sub test()
Cells(1, 3).Value = Cells(1, 1).Value - Cells(1,
2).Value * 0.000001
End Sub


with input data:
A1=0.035
A2=35000

I expect to get A3=0; however I get A3 = 6.94E-18!

Can anyone explain why and how this can be avoided?

Thank you in advance for your help,

Adrian F.
Ottawa

 

[Michael Malinsky]

The issue is with the level of precision provided by Excel. Excel only
provides precision to 15 decimal places. As a solution, I would use the
ROUND function in VBA to eliminate this problem, such as:

Cells(1, 3).Value = Cells(1, 1).Value - Round(Cells(1, 2).Value * 0.000001,
15)

Note that if you round to 18 decimal places instead of 15 (or whatever level
of precision you need), you will end up with the same answer as if you did
not round.

HTH

--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh

 

[Tom Ogilvy]

Public Sub test()
Cells(1, 3).Value = Format(Cells(1, 1).Value - _
Cells(1, 2).Value * 0.000001,"#0.0000000")
End Sub

It is due to the fact that all decimal numbers can not be represented
exactly using binary. Just like 1/3 can't be exactly represented using
Decimal numbers.