Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Function Bug ?
I believe this is an excel bug in which I will do my best to explain. Stick with me on this one. I am using Excel 2000 v9.0.2720 I have a column (column C) of numbers like this: C4 1,689,772.85 C5 (1,660,919.76) C6 0.00 C7 28,853.09 =sum(C4:C6) C8 C9 C10 28,853.09 =sum(C7:C9) C11 C12 8,187.14 C13 13,257.13 C14 50,297.36 =sum(C12:C13) C15 -363.80 C16 -3.46 C17 C18 C19 49,930.10 =sum(C14:C18) C20 C21 C22 49,930.10 =sum(C19:C21) C23 C24 C25 C26 C27 C28 49,930.10 =sum(C22:C27) C29 C30 C31 C32 ERROR This is formula: =if(C19=C28,0,"ERROR") Now, as you can see C19 DOES EQUAL C28 and therefore 0 should print in C32. If I change C7 from the SUM of C4:C6 and simply type in 28853.09 (Which is the same as the sum) then my formula on C32 works and the 0 prints in C32 Even more strange.... If I leave C7 as =sum(c4:c6) but change the numbers in C4 to C6 to 28854.09 and -1.00 respectively giving me the same 28853.09 as before the C32 formula works again ! I can type in pretty much any combination of numbers in C4 and C6 as long as the sum comes to 28853.09 and my C32 formula works BUT if I type in the numbers shown above in C4 and C6 my formula fails yet the sum is EXACTLY THE SAME !!! Any help anyone can give on this is greatly appreciated. Thanks. -- ddelong ------------------------------------------------------------------------ ddelong's Profile: http://www.excelforum.com/member.php...o&userid=25045 View this thread: http://www.excelforum.com/showthread...hreadid=385677 |
#2
|
|||
|
|||
This is not a bug, but rather the way Excel handles numbers. If you use the formula =ROUND(SUM(C4:C6),2) your problem should go away. You may need to 'round' other SUMs as well. Does this work for you? -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=385677 |
#3
|
|||
|
|||
Thank you, Thank you ! It worked. I've been going bananas on this one. Can you tell me what it is about those numbers that I used that would require me to round when I'm simply doing a sum ? Thanks again ! -- ddelong ------------------------------------------------------------------------ ddelong's Profile: http://www.excelforum.com/member.php...o&userid=25045 View this thread: http://www.excelforum.com/showthread...hreadid=385677 |
#4
|
|||
|
|||
I'm not good at explaining that, but it is the way binary computers deal with decimal numbers. It probably has your number as nnn.089999999 instead of .09. That is the quick and simple explanation. Someone may want to elaborate on this. Good Luck! As a follow up, I am curious about your post: you list the following: C12 8,187.14 C13 13,257.13 C14 50,297.36 =sum(C12:C13) when in fact, C12+C13=21,444.27 not 50,297.36 Is this a typo? Just wondering -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=385677 |
#5
|
|||
|
|||
Yes I had a typo in my sum, it should've been sum(c10:C13) good catch ! Thank you for the explanation but it still doesn't explain why I could use other numbers to add up to 28853.09 and be fine or even simply type 28853.09 in that field and not use the sum function. Should I use the round function EVERY time I use the sum function so that this can't happen ? If someone could elaborate I would appreciate it. -- ddelong ------------------------------------------------------------------------ ddelong's Profile: http://www.excelforum.com/member.php...o&userid=25045 View this thread: http://www.excelforum.com/showthread...hreadid=385677 |
#6
|
|||
|
|||
Hi ddelong,
You may want to have a look at this: http://support.microsoft.com/kb/78113/en-us Regards, KL "ddelong" wrote in message ... Yes I had a typo in my sum, it should've been sum(c10:C13) good catch ! Thank you for the explanation but it still doesn't explain why I could use other numbers to add up to 28853.09 and be fine or even simply type 28853.09 in that field and not use the sum function. Should I use the round function EVERY time I use the sum function so that this can't happen ? If someone could elaborate I would appreciate it. -- ddelong ------------------------------------------------------------------------ ddelong's Profile: http://www.excelforum.com/member.php...o&userid=25045 View this thread: http://www.excelforum.com/showthread...hreadid=385677 |
#7
|
|||
|
|||
See
http://mcgimpsey.com/excel/pennyoff.html and http://cpearson.com/excel/rounding.htm In article , ddelong wrote: Can you tell me what it is about those numbers that I used that would require me to round when I'm simply doing a sum ? |
#8
|
|||
|
|||
On Fri, 8 Jul 2005 15:24:05 -0500, ddelong
wrote: Yes I had a typo in my sum, it should've been sum(c10:C13) good catch ! Thank you for the explanation but it still doesn't explain why I could use other numbers to add up to 28853.09 and be fine or even simply type 28853.09 in that field and not use the sum function. Should I use the round function EVERY time I use the sum function so that this can't happen ? If someone could elaborate I would appreciate it. Jerry W Lewis writes about this frequently. And yes, if you are interested that two separate SUMS equate exactly to zero, you will either have to use the ROUND function, or use INTEGER math. Here is one of Jerry's previous postings about a similar issue. The concept is applicable to your problem: =========================== the easiest way to think about these issues is to recognize that Excel's documented limit of 15 decimal digit accuracy relates to the accuracy of decimal/binary conversion. Thus your problem can be thought of as -30584.0100000000????? 30585.6000000000????? ---------------------- 1.5900000000????? which is consistent with your result of 1.59000000000015 The binary approximation to 30584.01 is 8406868654754365/274877906944 The binary approximation to 30585.6 is 4203652855313203/137438953472 The exact difference between these numbers is 437055872041/274877906944 which in decimal is 1.59000000000014551915228366851806640625, or 1.59000000000015 to 15 figures. In keeping with Excel's documented limit of 15 decimal digit accuracy, if you ask for more than 15 decimal digits, the trailing digits are arbitrarily displayed as zero. Jerry =================================== --ron |
#9
|
|||
|
|||
You can see the impact of accumulating binary approximations by copying
cell C14 and Edit|Paste Special|Values you will see that the value is 50297.3600000001, instead of 50297.36. The sum in C7 is not visibly different from 28853.09, but =(C7-28853.09) shows that even here you have a small difference from what you expected. The issue is not the SUM function per se (the math is correct), but unavoidable approximations to the inputs. When you do math with approximate inputs, it should be no surprise that the result is only approximate. Almost all decimal fractions have no exact binary representation (just as 1/3 has no exact decimal representation). It is also worth noting that this applies to almost all computer software; Excel is representing numbers as specified in the IEEE standard. The original question was why =IF(C19=C28,0,"ERROR") returns "ERROR" instead of 0. I cannot reproduce this result given the formulas specified for C22 and C28, and the posted description that C20:C21 and C23:C27 are blank (so that C28 is equivalent to =C22 and C22 is equivalent to =C19. Possibly one or the other of C22 and C28 was hand enetered instead of calculated by the posted formulas? If C19:C28 were really as posted, what version of Excel was used? Jerry Ron Rosenfeld wrote: On Fri, 8 Jul 2005 15:24:05 -0500, ddelong wrote: Yes I had a typo in my sum, it should've been sum(c10:C13) good catch ! Thank you for the explanation but it still doesn't explain why I could use other numbers to add up to 28853.09 and be fine or even simply type 28853.09 in that field and not use the sum function. Should I use the round function EVERY time I use the sum function so that this can't happen ? If someone could elaborate I would appreciate it. Jerry W Lewis writes about this frequently. And yes, if you are interested that two separate SUMS equate exactly to zero, you will either have to use the ROUND function, or use INTEGER math. Here is one of Jerry's previous postings about a similar issue. The concept is applicable to your problem: =========================== the easiest way to think about these issues is to recognize that Excel's documented limit of 15 decimal digit accuracy relates to the accuracy of decimal/binary conversion. Thus your problem can be thought of as -30584.0100000000????? 30585.6000000000????? ---------------------- 1.5900000000????? which is consistent with your result of 1.59000000000015 The binary approximation to 30584.01 is 8406868654754365/274877906944 The binary approximation to 30585.6 is 4203652855313203/137438953472 The exact difference between these numbers is 437055872041/274877906944 which in decimal is 1.59000000000014551915228366851806640625, or 1.59000000000015 to 15 figures. In keeping with Excel's documented limit of 15 decimal digit accuracy, if you ask for more than 15 decimal digits, the trailing digits are arbitrarily displayed as zero. Jerry =================================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |