1 minus 1 doesn't equal 0 ?!!?!?

B

Ben

I'm not sure if I have a setting that is turned off, but I have some very
simple formulas.

A1 = 1
A2 = -1

When I sum, I get 0.000000000000000022. Not sure what the problem is. I
manually typed the numbers into celss A1 and A2.

Thanks,
Ben
 
S

Sheeloo

Are you sure you have 1.0000000000000000000000 and -1.0000000000000000000000?

I tested with what you have and got 0....
 
S

shg

I agree with Sheloo for the OP's question as stated. Integers up to
+/-10^15 can be exactly represented in floating point, and the result
should (and does) sum precisely to zero if manually entered (as opposed
to being the result of a formula that could have rounding errors).
 
J

Jerry W. Lewis

At least one of
=(A1-1)
=(A2+1)
will not be zero. Shane's reference explain in general terms why. If you
are still concerned, then post the equations that produced the values in
A1:A2 for a more specific explanation.

Jerry
 
J

joeu2004

I'm not sure if I have a setting that is turned off, but I have some
very simple formulas.
A1 = 1
A2 = -1
When I sum, I get 0.000000000000000022.  Not sure what the
problem is.  I manually typed the numbers into celss A1 and A2.
Click to expand...

Something is very wrong with what you state here. I doubt that you
"manually typed" the numbers 1 and -1 into A1 and A2, then produced
their sum by =A1+A2 or =sum(A1:A2).

All "small" integers can be represented exactly in Excel, despite the
banalities of binary representation. By "small", I mean up to +/-
2^53. That is 9,007,199,254,740,992 -- although Excel will permit you
to manually enter only the first 15 digits without incurring rounding
error (e.g. 9,007,199,254,740,980 and 9,007,199,254,740,990, but not
anything in between). To be sure, Excel can exactly represent all
integers between +/- 999,999,999,999,999 inclusively.

And if two such integers differ in sign -- one positive, the other
negative -- Excel can and does represent their sum exactly without
introducing any rounding error.

So either you did not manually enter 1 and -1 manually, or you are not
simply summing their cells.

If you truly did, there is something seriously wrong with your version
of Excel or with your environment. For example, you might have an
event macro that is altering the numbers after you enter them. That
is wild speculation and very unlikely. But I cannot think of any
other explanation, unless you retract your assertion that you entered
1 and -1 manually and you simply summing just those two cells.
 
N

Niek Otten

<I get 0.000000000000000022.>

That's odd; Excel's precision is only 15 decimal digits. You show 18.
Something else must be going on. If you do Format>Cells>Number tab>Custom,
what do you see in the Type box?
 
J

joeu2004

Something is very wrong with what you state here.  I doubt that you
"manually typed" the numbers 1 and -1 into A1 and A2, then produced
their sum by =A1+A2 or =sum(A1:A2).
Click to expand...

Another possibility: click on Tools > Options > Edit. Is Fixed
Decimal Places checked with some large number selected? If so,
uncheck the option.

When you type 1 and -1, that option would cause them to be entered as
different values in the cell. Normally, that should be apparent to
you because they would not appear as 1 and -1. But perhaps there is a
formating option that is causing them to appear as 1 and -1. (I
haven't thought it through completely.)

Arguably, even so, I would expect "1" and "-1" to have the same binary
representation except for their signs. So their sum should still be
zero.

Just grasping at straws here, trying to make sense of nonsense. I
think the more reasonable explanation is that you have not given us
all the facts correctly. Assuming you know what "manually typed"
means, the most likely explanation is that you are not simply adding
the two cells; at least, not in the most obvious ways.
 
J

Jerry W. Lewis

Likely the OP just typed too many zeros after the decimal point instead of
pasting the value from Excel (hint for posting: it is always better to
copy/paste rather than retyping).

For example, if A1 contains the value =(1+2^-49), and A2 contains the value
=-(1-2^-51), then both cells would appear to contain +/-1 to Excel's 15 digit
display limit, but =SUM(A1:A2) will give 2.22045E-15.

As noted in other posts, the OP's claim to have manually typed the numbers
is not consistent with any known arithmetic behavior in Excel (though the OP
might not realize that Paste|Special|Values from formula cells is not
equivalent to manually typing the displayed contents). As suggested earlier,
=(A1-1)
=(A2+1)
will diagnose what is happening, unless the OP has produced some stange
combination of the fixed decimal place option and unusual custom cell formats
that mislead about the cell contents. In that latter case,
=A1&""
=A2&""
would be informative.

Jerry
 
Top