Excel can't handle Zero

[SimonCampbell]

If I enter 1 in a cell (A1), then in cell A2 add the formula (=A1-0.5) I get 0.95. If I then copy this formula down A3..A21 I would expect the value zero in cell A21, but I get -3,2E-16. This is very close to zero but is not zero. WHY is this happening. Why cannot Excel calculate zero

[AliGW]

Have a look here: https://www.microsoft.com/en-us/micr...wrong-answers/

and here: https://www.spreadsheetweb.com/float...-issues-excel/

[SimonCampbell]

Many thanks... but I must say I'm none the wiser... anyway.. it's clear it's an excel problem.... I had thought perhaps it might have something to do with the Processor in my PC. I hope they don't use excel at NASA, calculating decent to the lunar surface would mean they'd crash

[Glenn Kennedy]

I doubt it!! Your washing machine probably has more processing power than Apollo 11 had!!! In all seriousness, don't run excel with more decimal places than are justified by your data. Don't change the formatting, as that does nothing to the underlying data. Use ROUND.

[popipipo]

Please Login or Register  to view this content.

Use this formula and cel A21 is exact zero

[MrShorty]

it's clear it's an excel problem.... I had thought perhaps it might have something to do with the Processor in my PC.

Actually, your first thought was more correct -- it does have to do with the processor in your PC. Floating point arithmetic has been an inherent part of the way computers perform calculations since -- will Wikipedia claims that the first floating point "computer" was invented over 100 years ago in 1914. The modern floating point standards came together in the early 1980's, and, as far as I know, every modern computer and programming language implements floating point arithmetic. https://en.wikipedia.org/wiki/Floating_point#History

The thing that surprises me about floating point error is not that it exists. Rather, I am frequently surprised at how many programmers seem completely unaware that this is how their processor performs arithmetic. We are supposed to be the most computer literate generation, and yet we seem blissfully unaware of this most fundamental part of how a computer works.

[Special-K]

Can I just add (no pun intended) that

1-0.5 is 0.5

"If I enter 1 in a cell (A1), then in cell A2 add the formula (=A1-0.5) I get 0.95"

I presume the OP means 1-0.05

[Aardigspook]

I am frequently surprised at how many programmers seem completely unaware that this is how their processor performs arithmetic. We are supposed to be the most computer literate generation, and yet we seem blissfully unaware of this most fundamental part of how a computer works.

Computer literate can mean 'good at using a computer' or 'understands how the computer works' - most people fall into the first camp. I usually try to explain FP problems by using 2/3 as a basis for some simple calculations, but allowing only three decimal places - it soon goes 'wrong' obviously... (3 x 2/3 <> 3 x 0.667).
That's before moving onto how everything in all computers is in binary, not b10, which just blows most people's minds...

@Simon Campbell, sorry to hijack your thread!