If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
#15
|
|||
|
|||
Strange Calculation Error in Excel (2)
|
#16
|
|||
|
|||
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. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Strange Excel Problem..... | Sachin Shah | General Discussion | 0 | November 14th, 2005 09:36 AM |
Strange result in Excel 2000 | ibertram | General Discussion | 4 | November 12th, 2005 01:48 PM |
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | MEGTOM | New Users | 5 | October 27th, 2005 03:06 AM |
Excel Range Value issue (Excel 97 Vs Excel 2003) | Keeno | General Discussion | 2 | June 13th, 2005 02:01 PM |
Coverting Lotus 123 files to use with excel | Muffin1947 | General Discussion | 6 | June 20th, 2004 10:18 AM |