Excel not adding negative numbers correctly

Question

Ok this might look like a stupid question but im stumped. I already have done this in the previous two columns but it is not working for the last two. Basically i have to zero out the values at the start and add the same value to the remaining numbers. Have done it 2 times but the last 2 are giving me problems. Below  are some pictures to show. I have tried almost everything and only thing that works is going in to the actual cell and manually typing the numbers but with 300+sums to do that might take a long time. Thank you

Answer 1

What you hare experiencing is called round off and results from Excel (and most other math apps) storing decimal number in binary format with a fixed number of bytes. This results in some decimals numbers not having exact binary representation. So we can get small numbers like 0.00000000000000577 showing up when zero is expected. Nothing to do with negative numbers.

Rounding to say 12 decimal places will overcome this.

Workaround: replace your formulae =H2+$P$3 by =ROUND(H2+$P$3,12)

best wishes

For more details:

Chip's clear explanation

http://www.cpearson.com/excel/rounding.htm

Floating-point arithmetic may give inaccurate results in Excel http://support.microsoft.com/kb/78113/en-us

(Complete) Tutorial to Understand IEEE Floating-Point Errors http://support.microsoft.com/kb/42980

Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”

http://blogs.office.com/b/microsoft-excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx

What Every Computer Scientist Should Know About Floating Point

http://docs.sun.com/source/806-3568/ncg\_goldberg.html

Visual Basic and Arithmetic Precision

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q279/7/55.ASP&NoWebContent=1

Good reading from T Valko

http://blogs.msdn.com/excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx

Others:

http://support.microsoft.com/kb/214118

http://blogs.msdn.com/b/excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx

http://docs.sun.com/source/806-3568/ncg\_goldberg.html