Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 minus 1 doesn't equal 0 ?!!?!?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 minus 1 doesn't equal 0 ?!!?!?
This is simple rounding error. To be more precise use:
=ROUND(A1+A2,8) -- Gary''s Student - gsnu200811 "Ben" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 minus 1 doesn't equal 0 ?!!?!?
Are you sure you have 1.0000000000000000000000 and -1.0000000000000000000000?
I tested with what you have and got 0.... "Ben" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 minus 1 doesn't equal 0 ?!!?!?
Hi,
Computers work in binary, we work in decimals Here is everything you need to know about this issue (and more): http://support.microsoft.com/kb/78113/en-us http://support.microsoft.com/kb/42980 http://support.microsoft.com/kb/214118 http://www.cpearson.com/excel/rounding.htm http://docs.sun.com/source/806-3568/ncg_goldberg.html If this helps, please cliick the Yes button. -- Thanks, Shane Devenshire "Ben" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 minus 1 doesn't equal 0 ?!!?!?
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). -- shg ------------------------------------------------------------------------ shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24483 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 minus 1 doesn't equal 0 ?!!?!?
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 "Ben" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 minus 1 doesn't equal 0 ?!!?!?
On Nov 3, 7:09*am, Ben wrote:
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. 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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 minus 1 doesn't equal 0 ?!!?!?
<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 FormatCellsNumber tabCustom, what do you see in the Type box? -- Kind regards, Niek Otten Microsoft MVP - Excel "Ben" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 minus 1 doesn't equal 0 ?!!?!?
On Nov 3, 9:57*am, joeu2004 wrote:
On Nov 3, 7:09*am, Ben wrote: *I manually typed the numbers into celss A1 and A2. 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). 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
1 minus 1 doesn't equal 0 ?!!?!?
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 "Niek Otten" wrote: <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 FormatCellsNumber tabCustom, what do you see in the Type box? -- Kind regards, Niek Otten Microsoft MVP - Excel "Ben" wrote in message ... 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 cells A1 and A2. Thanks, Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i set formula to be equal to a value plus or minus 1? | Excel Worksheet Functions | |||
can I make cell "yes" equal 1, "no" equal 0 | Excel Discussion (Misc queries) | |||
How to change all cells from minus to plus and plus to minus | Excel Worksheet Functions | |||
Greater than or equal to (Plus or minus) | Excel Discussion (Misc queries) | |||
CHANGE TRAILING MINUS TO BRACKETS OR PRECEEDING MINUS | Excel Discussion (Misc queries) |