Can anyone explain this to me please?
Hi,
Can anyone explain this highly irritating issue please?
I'm using the following formula:
=IF(A146=0,0,E145+C146-D146)
where E145 = -5089.22
and C146 = 5089.22
and D146 = 0
which produces effectively -5089.22 + 5089.22 as shown below.
Capture 1.JPG
This then calculates at the next step as 1.8189E-12 rather than 0.
Capture 2.JPG
Yet when I do E145 + C146 it works out fine.
Any ideas anyone please????!!!!
Floating point arithmetic errors. Good explanation provided here: http://support.microsoft.com/kb/78113
The ROUND function takes care of it most elegantly.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Your computer stores numbers in binary floating-point format. In the same sense that 1/3 is a repeating decimal in base 10, and so cannot be repeated exactly with any finite number of digits, many values (like 0.1) are repeating decimals in base 2.
When testing equality, you need to include some rounding or a tolerance:
=if(abs(...) < 1e-6, ..., ...)
Entia non sunt multiplicanda sine necessitate
Thanks.
Very irritating still.
It could not be any other way.
Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.
To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks