Forum Discussion
Solved
I found a bug
"=128.52 - 124.52 = 4"
it should be "TRUE"
but "FALSE" came out.
there's a lot of combination like this:
128.52-118.52=10
128.51-120.51=8
....
- This isn't really a bug. This is due to roundoff errors. In order to calculate 128.52 - 124.52 it must represent each number in a binary representation system but clearly 128.52 and 124.52 are not easily represented in binary and result in a round off and if 1 number rounds off different than the other (as in this case) you get a very small and usually insignificant error; in this case 4.00000000000001. You can see this by just entering =128.52 - 124.52 in a cell and then show 14 digits of resolution. There are some very well written comments/answers in this forum about the exact detail of how this all works if you are interested. So yes, Excel has limitations on its resolution. So this example equation should force a round off something like this to prevent such error: = ROUND(128.52 - 124.52,10) = 4
2 Replies
- Here is a reference with good link for more information: https://techcommunity.microsoft.com/t5/excel/incorrect-15th-decimal-causing-rounding-errors/m-p/1211483#M54460
- This isn't really a bug. This is due to roundoff errors. In order to calculate 128.52 - 124.52 it must represent each number in a binary representation system but clearly 128.52 and 124.52 are not easily represented in binary and result in a round off and if 1 number rounds off different than the other (as in this case) you get a very small and usually insignificant error; in this case 4.00000000000001. You can see this by just entering =128.52 - 124.52 in a cell and then show 14 digits of resolution. There are some very well written comments/answers in this forum about the exact detail of how this all works if you are interested. So yes, Excel has limitations on its resolution. So this example equation should force a round off something like this to prevent such error: = ROUND(128.52 - 124.52,10) = 4