Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel
Hi. I have a table of numbers, and I do some simple Math based on this data. Eg: 4.1 4.1 3.97 4.03 4.15 4.15 4.1 4.1 For the formula "=A4-B4", the answer given is "-0.069999999999999400000000000000" It should be just -0.07. Then I checked the value for A4 & B4, the values a 4.030000000000000000000000000000 4.100000000000000000000000000000 What's wrong with Excel? How can I correct this error? PS: If anyone wishes to see the original Excel file, please leave your email address here. Thanks! -- Additional information: - I'm using Office XP - I'm using Windows XP ¥»¤Hªº¯à¤O«D±`¦³**. ¦p¦³¤£·í¤§³B, ±æÃѪ̤£§[½ç¥¿!! My ability is very limited. Hope you will not mind to enlighten me if I do wrongly. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel
You can't correct the error. It's how computers have worked since day one. It
happens because computers convert from decimal to binary, and not all conversions are perfect. To avoid to problem, use the Round function, as in =Round(a4-b4,2) -- Regards, Fred "0-0 Wai Wai ^-^" wrote in message ... Hi. I have a table of numbers, and I do some simple Math based on this data. Eg: 4.1 4.1 3.97 4.03 4.15 4.15 4.1 4.1 For the formula "=A4-B4", the answer given is "-0.069999999999999400000000000000" It should be just -0.07. Then I checked the value for A4 & B4, the values a 4.030000000000000000000000000000 4.100000000000000000000000000000 What's wrong with Excel? How can I correct this error? PS: If anyone wishes to see the original Excel file, please leave your email address here. Thanks! -- Additional information: - I'm using Office XP - I'm using Windows XP ¥»¤Hªº¯à¤O«D±`¦³**. ¦p¦³¤£·í¤§³B, ±æÃѪ̤£§[½ç¥¿!! My ability is very limited. Hope you will not mind to enlighten me if I do wrongly. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel
"Fred Smith" ¦b¶l¥ó ¤¤¼¶¼g... You can't correct the error. It's how computers have worked since day one. It happens because computers convert from decimal to binary, and not all conversions are perfect. To avoid to problem, use the Round function, as in =Round(a4-b4,2) Thanks for your reply. But the solution is not practical since I need to do so for everything I calculate in Excel. Take the above example again: C4: =A4-B4 D4: =countif(C4, -0.07) The answer is 0. Too bad! It should be 1. Your answer did eliminate the problem, but I have many different formulas which are baffled by this strange calculation error. It is tons of rewriting. Oh no!! Is there any practical solution I would take to workaround this "calcualtion error"? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel
"0-0 Wai Wai ^-^" wrote:
"Fred Smith" : To avoid to problem, use the Round function, as in =Round(a4-b4,2) Thanks for your reply. But the solution is not practical since I need to do so for everything I calculate in Excel. [....] I have many different formulas which are baffled by this strange calculation error. It is tons of rewriting. Oh no!! Try setting Tools Options Calculation Precision As Displayed. Of course, then you might need to adjust the format of some cells to be sure that they display the needed precision. This includes "helper" cells, which you might have hidden. Caveat emptor. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel
" ¦b¶l¥ó ¤¤¼¶¼g... "0-0 Wai Wai ^-^" wrote: "Fred Smith" : To avoid to problem, use the Round function, as in =Round(a4-b4,2) Thanks for your reply. But the solution is not practical since I need to do so for everything I calculate in Excel. [....] I have many different formulas which are baffled by this strange calculation error. It is tons of rewriting. Oh no!! Try setting Tools Options Calculation Precision As Displayed. Of course, then you might need to adjust the format of some cells to be sure that they display the needed precision. This includes "helper" cells, which you might have hidden. Caveat emptor. Hi. Is it possible to set something like it is displayed up to 2 decimal places, but the precision holds up to 10 decimal places or so? I don't wish to show all figures up to 10 decminal places. It is just too clumsy. And since this setting is global, it is still not preferable. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel
Hi Wai Wai,
You might want to try my Excel add-in, xlPrecision 2.0. It avoids these errors by never converting to binary. You can download the free edition of xlPrecision 2.0 from here and use it as long as you wish: http://PrecisionCalc.com Good Luck, Greg Lovern http://PrecisionCalc.com Eliminate Hidden Spreadsheet Errors |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel
You might want to try my Excel add-in, xlPrecision 2.0.
It avoids these errors by never converting to binary. [....] Greg Lovern http://PrecisionCalc.com Eliminate Hidden Spreadsheet Errors Fascinating! I would imagine it slows computation tremendously. Do you have any performance comparisons? Would be better if Excel itself implemented BCD, at least as an option. With nano-to-picosec instruction times, it is probably a reasonable trade-off. I hope Bill is listening :-). |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel
Hi Joe,
I would imagine it slows computation tremendously. Do you have any performance comparisons? < I haven't benchmarked it against rounding in Excel, but obviously it's slower than Excel. xlPrecision's main purpose is high precision (i.e., more than 15 significant digits), and one reasonably expects to sacrifice performance for that. Avoiding binary conversion errors is a happy side effect. I heard praise, and no complaints, for version 1.0's performance (for high precision), and 2.0 is faster. And the next version will be faster still. Thanks, Greg Lovern http://PrecisionCalc.com Eliminate Hidden Spreadsheet Errors |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel (2)
0-0 Wai Wai ^-^ wrote:
It doesn't really matter how the number is displayed after, say, 15 decimal points. But what I want is it can still be calculated without being affected by this minor mistake. Calling it a "mistake" suggests that you still do not understand. It is an inevitable consequence of finite precision mathematics. Suppose you were doing decimal math with 4-figure precision. Then (4+1/3)-(4+2/3) would be 4.333-4.667 = -0.334 There is no mistake, but the result is numerically different from the representation of 1/3 = 0.3333 in this system. You are presumably aware that numbers like 1/3, 1/7, 1/11, etc. cannot be exactly represented in decimal, and so you are not surprised when numbers like these have to be approximated. The only additional surprise here is that numbers like 1/5 are also non-terminating binary fractions, with the result that most finite decimal fractions (including ..03, .07, .1, .15, and .97) can only be approximated. When you do math with approximate inputs, you should not be surprised when the result is also an approximation. It is not an "error", "mistake", "imperfect conversion", etc. it is just the nature of the beast. Converting to BCD as joeu2004 suggested would not eliminate the problem, as my decimal example illustrated. It would just confine the problem (finite precision approximation to numbers that can only be exactly represented in infinite precision) to numbers where we more readily recognize what has happened. BCD is rarely done in computers, because it is relatively wasteful and slow, which seems a steep price to pay for a "solution" that doesn't fully solve the problem. Extended precision packages like xlPrecision also do not solve the problem, they just push it farther out (though they do have their uses). The only way to completely solve the problem is to do symbolic math http://en.wikipedia.org/wiki/Compute...ebra_s ystems But the performance penalty from that option would be totally unacceptable for large spreadsheets. Just like the countif function. It can't calculate well due to the small difference of 0.00....005 Any workaround is appreciated. If you are unwilling to standardize the approximations (using ROUND() on the calculations or setting the Precision as Displayed option), then you need to do comparisons that are robust to approximations. Examples would include IF(ROUND(C4,2)=0.07,... or IF(ABS(C4-0.07)<0.005,... For summarizing a range, this would generally require array formulas. Jerry |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel (2)
"Jerry W. Lewis" ??? ???... 0-0 Wai Wai ^-^ wrote: It doesn't really matter how the number is displayed after, say, 15 decimal points. But what I want is it can still be calculated without being affected by this minor mistake. Calling it a "mistake" suggests that you still do not understand. It is an inevitable consequence of finite precision mathematics. Thanks for your explanation. I did know a bit after the first reply. At that time, I searched for information about this problem. Just a thought to me. Since it is an inevitable consequence is notihng to do to say whether it is not a mistake. Humans always make mistakes. It is an inevitable consequence in our life. But does that mean they are no longer mistakes then since they are inevitable? Inevitablity is nothing to do with classifying a mistake. Anyway, this correspondence is just a casual one. Thus not every word is carefully thought out before written. Maybe "this sort of problem" should not be called mistake since it seems I am blaming my computer without any appreciation of its limitation. Maybe "error", or "natural beast" is a better name for "this problem". Suppose you were doing decimal math with 4-figure precision. Then (4+1/3)-(4+2/3) would be 4.333-4.667 = -0.334 There is no mistake, but the result is numerically different from the representation of 1/3 = 0.3333 in this system. You are presumably aware that numbers like 1/3, 1/7, 1/11, etc. cannot be exactly represented in decimal, and so you are not surprised when numbers like these have to be approximated. The only additional surprise here is that numbers like 1/5 are also non-terminating binary fractions, with the result that most finite decimal fractions (including .03, .07, .1, .15, and .97) can only be approximated. When you do math with approximate inputs, you should not be surprised when the result is also an approximation. It is not an "error", "mistake", "imperfect conversion", etc. it is just the nature of the beast. Converting to BCD as joeu2004 suggested would not eliminate the problem, as my decimal example illustrated. It would just confine the problem (finite precision approximation to numbers that can only be exactly represented in infinite precision) to numbers where we more readily recognize what has happened. BCD is rarely done in computers, because it is relatively wasteful and slow, which seems a steep price to pay for a "solution" that doesn't fully solve the problem. Extended precision packages like xlPrecision also do not solve the problem, they just push it farther out (though they do have their uses). The only way to completely solve the problem is to do symbolic math http://en.wikipedia.org/wiki/Compute...ebra_s ystems But the performance penalty from that option would be totally unacceptable for large spreadsheets. Just like the countif function. It can't calculate well due to the small difference of 0.00....005 Any workaround is appreciated. If you are unwilling to standardize the approximations (using ROUND() on the calculations or setting the Precision as Displayed option), then you need to do comparisons that are robust to approximations. Examples would include IF(ROUND(C4,2)=0.07,... or IF(ABS(C4-0.07)<0.005,... For summarizing a range, this would generally require array formulas. I'm willing to use round(), but there are tons of rewriting. It seems to be impossible to rewrite all of them by human. It would be great if you could suggest a method which can rewrite 1000 formulas automatically. As to "Precision as Displayed", it is a bad idea since I will either sacrifice precision or force me to display 10-decimal-point for every figure (clumsy looking :( Anyway, I just wonder why countif won't work under its "binary-to-decimal" problem. If, say, computers can only store 0.06999999...9994 for 0.07, so when I type 0.07, computers should actually treat it as 0.069999999...9994 (since it can't store 0.07 precisely). Hmm... I know I am probably asking stupid questions. But when I type countif(A1,0.07), it won't count it. What does it imply? Doesn't it mean computers can still store 0.07? To computers, 0.07 or 10-9.93 should mean the same as computers, ie 0.06999...9994. But from the result, it seems computer read the first one as 0.07, the second as 0.06999...9994. OK, I'm going idiotic. X( |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel (2)
Sub RoundAdd()
Dim myStr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=ROUND(*" Then myStr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=ROUND(" & myStr & "," & "2" & ")" End If End If Next End Sub Gord Dibben Excel MVP On Sat, 3 Dec 2005 23:20:10 +0800, "0-0 Wai Wai ^-^" wrote: I'm willing to use round(), but there are tons of rewriting. It seems to be impossible to rewrite all of them by human. It would be great if you could suggest a method which can rewrite 1000 formulas automatically. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel (2)
Hi Jerry,
Extended precision packages like xlPrecision also do not solve the problem, they just push it farther out (though they do have their uses). < This is true of some extended precision packages, but not true of xlPrecision. xlPrecision never converts anything to binary. xlPrecision does all arithmetic in base 10. Using xlPrecision results in no more binary conversion errors than doing arithmetic in longhand (i.e., pencil and paper). Also, I may be a little foggy on the definition, but I'm not sure that it's quite accurate to refer to xlPrecision as "extended" precision. xlPrecision is *arbitrary* precision in the sense that the underlying algorithms have no maximum number of significant digits. xlPrecision's maximum of 32,767 significant digits is simply the result of Excel's limit of that many characters in a cell. I could easily extend that by allowing array-entering into multiple cells, but I haven't done that because I haven't heard of anyone wanting more than 32,767. If I were to do so, the next limit I would reach is the largest text string variable allowed, which would be a little over 2 billion significant digits. Even that could be easily overcome by using arrays. Again, the reason I haven't done it is because I don't think anyone would be interested in that many significant digits. Thanks, Greg Lovern http://PrecisionCalc.com Eliminate Hidden Spreadsheet Errors |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel (2)
Don't forget to use this on a copy of your worksheet.
Macros disable the "undo" function. Gord On Sat, 03 Dec 2005 08:23:57 -0800, Gord Dibben <gorddibbATshawDOTca@ wrote: Sub RoundAdd() Dim myStr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=ROUND(*" Then myStr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=ROUND(" & myStr & "," & "2" & ")" End If End If Next End Sub Gord Dibben Excel MVP On Sat, 3 Dec 2005 23:20:10 +0800, "0-0 Wai Wai ^-^" wrote: I'm willing to use round(), but there are tons of rewriting. It seems to be impossible to rewrite all of them by human. It would be great if you could suggest a method which can rewrite 1000 formulas automatically. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel (2)
0-0 Wai Wai ^-^ wrote:
As to "Precision as Displayed", it is a bad idea since I will either sacrifice precision or force me to display 10-decimal-point for every figure (clumsy looking :( I tend to agree. Some financial calculations are the only context I can think of where I would be comfortable with Precision as Displayed. Anyway, I just wonder why countif won't work under its "binary-to-decimal" problem. If, say, computers can only store 0.06999999...9994 for 0.07, so when I type 0.07, computers should actually treat it as 0.069999999...9994 (since it can't store 0.07 precisely). Hmm... I know I am probably asking stupid questions. But when I type countif(A1,0.07), it won't count it. What does it imply? Doesn't it mean computers can still store 0.07? To computers, 0.07 or 10-9.93 should mean the same as computers, ie 0.06999...9994. But from the result, it seems computer read the first one as 0.07, the second as 0.06999...9994. OK, I'm going idiotic. X( Re-examine my decimal example. If you did a search for -1/3, would you expect it to find -0.334, when -1/3 would calculate as -0.3333? What happened in my decimal example, is that while the input numbers were accurate to 4 figures, the subtraction canceled the first figure, so the result was accurate to roughly 3 figures. Similarly with your problem, the subtraction in =4.03-4.1 cancels 6 of the 53 bits used in the binary representation of these numbers. Excel will not display more than 15 meaningful digits (documented in Help for "Excel specifications and limits"). Consequently the approximations involved in representing 4.03 and 4.1 are not apparent, but after canceling those 6 bits, the result of these approximations is visible in the answer. The closest you can approximate these numbers based on 53-bit accuracy in the mantissa is 4.030000000000000248689957516035065054893493652343 75 -4.099999999999999644728632119949907064437866210937 5 ----------------------------------------------------- -0.069999999999999396038674603914842009544372558593 75 which Excel correctly displays to 15 digits as -0.0699999999999994 But the closest 53-bit approximation to 0.07 is -0.070000000000000006661338147750939242541790008544 921875 The difference between these two representations is 0.000000000000000610622663543836097232997417449951 171875 which Excel correctly displays to 15 digits as 0.000000000000000610622663543836 It is this difference (analogous to the difference between -0.334 and -0.3333 in my decimal example) that Excel is detecting when you try to do COUNTIF(C4,-0.07) You can see more than 15 digits of the binary representation of numbers in Excel by using the VBA functions that I posted at http://groups.google.com/group/micro...fb95785d1eaff5 But you can easily predict the magnitude of approximation without going to such lengths. Just think in terms of the documented 15 figure limit. Your problem is then 4.03000000000000??? -4.10000000000000??? -------------------- -0.07000000000000??? vs. the calculated result of 0.000000000000000610622663543836 Also, remember that this is not an Excel issue, rather it is a finite mathematics issue compounded by approximations necessary in decimal/binary conversions. Excel follows the IEEE standard for internal representation of numbers, and so is no more or less accurate than almost all general purpose software. I know its a lot to take in at once, between this and your array formula thread, but it will pay off in the long run. Cheers, Jerry |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Strange Calculation Error in Excel
Hi Fred --
I understand the reason why decimals will not always convert perfectly to binary, but the similar problem that I kept running into (primarily using Lotus 1-2-3 Ver 1A -- I've only recently had to start working in Excel, and so far have had only one occasion to do a work-around in that program) is that I would get floating point errors when adding WHOLE NUMBERS that mathematically should sum to zero. Surely whole numbers should convert perfectly to binary. I frequently needed to test whether a sum equaled zero, but the best fix I could come up with was "if(abs([formula])<0.001,[do A],[do B]). Why adding and subtracting whole numbers would yield these same 15th decimal place discrepancies is completely beyond my comprehension. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How can I do an hourly salary calculation in Excel | Excel Discussion (Misc queries) | |||
Excel: Area under a curve calculation | Charts and Charting in Excel | |||
Really Strange Excel Issue | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) |