Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed: Calculation error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed: Calculation error
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 "Adrian" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help Needed: Calculation error
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. -- Regards, Tom Ogilvy "Adrian" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Payroll Calculation - Help Needed | Excel Discussion (Misc queries) | |||
Formula for the calculation needed to come up with a total | Excel Worksheet Functions | |||
Calculation with EDATE Help Needed! | Excel Worksheet Functions | |||
Help needed with a calculation question | Excel Worksheet Functions | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions |