Excel calculation is 0, but displays 1.77636E-15

Topic

New Reply

Hi all,

I’ve got a calculation which returns 0, but in some of the cells instead of displaying zero it displays 1.77636E-15.

I know this is some kind of weird excel floating point issue, but what can I do to display 0?

Thanks

Moz

Reply | Quote

Replies

Format the cell to display as a number with 0 decimal places.

Reply | Quote

Moz,

I find it’s a good idea to almost always use the Round function and specify the number of decimal places I want. Early in my dual track career when I was doing a lot of accounting invariably someone would point out that the column didn’t add up because the total was .01 greater than the displayed numbers, darn accountants! Rounding all the calculations to 2 decimal places { =Round(A1/B7,2) } solved that problem and became my standard practice. YMMV. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

What you are observing is one of the limitations of floating point calculations done in binary on a computer; it’s not specific to Excel.

You can also observe a similar effect on some calculators: do 1 ÷ 7 x 7 = You expect the answer 1, but on cheap calculators you’ll get a result like 0.9999997

BATcher

Plethora means a lot to me.

Reply | Quote

Thanks for your help, I’ll try the rounding method.

Moz

Reply | Quote