

December 12, 2016

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.


July 16, 2010

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


December 12, 2016

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.


November 8, 2013

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-u.....g-answers/


December 12, 2016

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.

Answers Post
1 Guest(s)
