I have a worksheet using a simple formula SUMIFS in Balance column. It works well for most of the calculations with one exception - It displayed value Zero with Scientific notation instead. Thanks for helping me dig out the problems.
Hi Julian,
This cell returns: 0.000000000000002
Excel thinks it's being helpful by displaying it in scientific notation because it's such a tiny number. If you don't like it displaying like that, then you can apply a number format to the column that displays, say, 2 decimal places instead.
Mynda
Thanks again for your quick and handy solution. However, except for the cell A148 I did supply a non-integer number 0.298 and down to A265 I supplied another negative number -.2985 to offset it, then the value in cell B265 returned to an integer number again. Thereafter, no more numbers with decimal point displayed from B265 downward. Why it came out such a tiny number 0.000000000000002 as you mentioned in cell B1257. It really confused me badly. Would you please tell me what’s your further findings ? It must be much helpful for me. Thanks.
Hi Julian,
It's much more complex than you expect.
Please take a look at this article that describes the floating point calculation problem: https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/
Catalin, Thank you for the clear message. It's really a floating point calculating issue as Microsoft's Diego Oppenheimer explained. I tried to change the value of cell A148 from .298 to .29 and the value of A265 from -.298 to -.29 then no more scientific notation appeared. I never thought it would happen like this way.
Normally, you should fix it using a Round function in B column, to 2-3 or more decimal places, a number like 0.000000000000002 will be rounded to 0, even if you have rounded to 10 decimal places.
