Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
Multiplying 111111111 by 111111111 in Excel 2007 results in
12345678987654300, which is obviously wrong. Is there a way to improve the accuracy of results over 15 digits? Thanks, Brian |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
Excel's precision is 15 decimal digits. See
http://support.microsoft.com/kb/78113 There are add-ins available to calculate with more digits. Be careful: you can't process those numbers with standard Excel formulas and functions. Look he http://digilander.libero.it/foxes/MultiPrecision.htm and http://precisioncalc.com/index.html But if you just need more digits for any identifiers, such as credit card numbers or product id's, store them as text, not as numbers -- Kind regards, Niek Otten Microsoft MVP - Excel "brianpo" wrote in message ... Multiplying 111111111 by 111111111 in Excel 2007 results in 12345678987654300, which is obviously wrong. Is there a way to improve the accuracy of results over 15 digits? Thanks, Brian |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
"brianpo" wrote:
Multiplying 111111111 by 111111111 in Excel 2007 results in 12345678987654300, which is obviously wrong. Is there a way to improve the accuracy of results over 15 digits? The short answer is "no", at least not with the standard product. There are add-ins available that do multi-precision arithmetic. I know nothing about them. Although Excel displays only the first 15 significant digits, arithmetic is performed to highest degree permitted by the standard binary format that it uses internally (i.e. IEEE 754). For example, the product of your numbers results in exactly 12345678987654320. That still does not match hand calculations; "missed it by t-h-a-t much" ;-) (It is off by only one in this case.) But it is "correct" insofar as it is consistent with the binary result that most applications will yield. The next binary representation is 12345678987654322. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
On Wed, 26 Aug 2009 20:39:01 -0700, brianpo
wrote: Multiplying 111111111 by 111111111 in Excel 2007 results in 12345678987654300, which is obviously wrong. Is there a way to improve the accuracy of results over 15 digits? Thanks, Brian You could download and install the XNUMBERS addin for EXCEL which performs multiprecision floating point arithmetic up to 250 significant digits. By default, it uses the precision of 30 digits. You can Google for a source. I believe the latest version is 5.6 --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
Now that every one else has waded in I will ask the question... Why would you
need more than 15 digits of accuracy? With that many digits I can get the distance from here to the sun to less than a millimeter. Just curious. -- HTH... Jim Thomlinson "brianpo" wrote: Multiplying 111111111 by 111111111 in Excel 2007 results in 12345678987654300, which is obviously wrong. Is there a way to improve the accuracy of results over 15 digits? Thanks, Brian |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
And if you really cared about this kind of accuracy, why would you be using
excel--a general purpose spreadsheet program??? Jim Thomlinson wrote: Now that every one else has waded in I will ask the question... Why would you need more than 15 digits of accuracy? With that many digits I can get the distance from here to the sun to less than a millimeter. Just curious. -- HTH... Jim Thomlinson "brianpo" wrote: Multiplying 111111111 by 111111111 in Excel 2007 results in 12345678987654300, which is obviously wrong. Is there a way to improve the accuracy of results over 15 digits? Thanks, Brian -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
< And if you really cared about this kind of accuracy, why would you be
using excel--a general purpose spreadsheet program??? Well, sometimes people just don't know. There was a time I didn't know about the "limited" precision. I'm in the financial business and I've never found span of precision a problem. But I did have problems with the fact that some numbers can not be represented exactly. If you round to (for example) a whole number, two calculation methods can lead to two totally different results. For example one calulation method results in 15.4444444444449, the other one yields 15.5000000000001. Then one rounds to 15, the other one rounds to 16. But there are sometimes unexpected effects. Take the example of the distance to the sun. What if it were not the distance but the angle you had to fire a rocket? For some interesting examples, look he http://www.eecs.berkeley.edu/~wkahan/Mind1ess.pdf In the newsgroups, two behaviors if Excel are often confused. One is that the number shown may not be the actual value (the "penny-off" problem), the other is the fact that not all numbers can be represented exactly. That last group is often confused because binary representation and floating-point computation are often not treated as separate issues. In fact, binary representations can mimic all decimal calculations, as long as you don't try to save on the number of bits. Good examples, sometimes even in hardware, can be found in BCD (Binary Coded Decimal) representaions, but that is certainly not the only method. Even in VBA, (so close to Excel!), you can find data types like Currency. I keep calling for decimal arithmatic in Excel, as an option. A very large percentage of the questions in the newgroups have to do with the fact that Excel does not calculate the way its users do. -- Kind regards, Niek Otten Microsoft MVP - Excel "Dave Peterson" wrote in message ... And if you really cared about this kind of accuracy, why would you be using excel--a general purpose spreadsheet program??? Jim Thomlinson wrote: Now that every one else has waded in I will ask the question... Why would you need more than 15 digits of accuracy? With that many digits I can get the distance from here to the sun to less than a millimeter. Just curious. -- HTH... Jim Thomlinson "brianpo" wrote: Multiplying 111111111 by 111111111 in Excel 2007 results in 12345678987654300, which is obviously wrong. Is there a way to improve the accuracy of results over 15 digits? Thanks, Brian -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
I remember trying to explain to someone why their average golf scores (with
handicap??) that were calculated in excel didn't match what they got with their $2 calculator. I kept getting a "yeah, but my calculator can do it!" response. Niek Otten wrote: < And if you really cared about this kind of accuracy, why would you be using excel--a general purpose spreadsheet program??? Well, sometimes people just don't know. There was a time I didn't know about the "limited" precision. I'm in the financial business and I've never found span of precision a problem. But I did have problems with the fact that some numbers can not be represented exactly. If you round to (for example) a whole number, two calculation methods can lead to two totally different results. For example one calulation method results in 15.4444444444449, the other one yields 15.5000000000001. Then one rounds to 15, the other one rounds to 16. But there are sometimes unexpected effects. Take the example of the distance to the sun. What if it were not the distance but the angle you had to fire a rocket? For some interesting examples, look he http://www.eecs.berkeley.edu/~wkahan/Mind1ess.pdf In the newsgroups, two behaviors if Excel are often confused. One is that the number shown may not be the actual value (the "penny-off" problem), the other is the fact that not all numbers can be represented exactly. That last group is often confused because binary representation and floating-point computation are often not treated as separate issues. In fact, binary representations can mimic all decimal calculations, as long as you don't try to save on the number of bits. Good examples, sometimes even in hardware, can be found in BCD (Binary Coded Decimal) representaions, but that is certainly not the only method. Even in VBA, (so close to Excel!), you can find data types like Currency. I keep calling for decimal arithmatic in Excel, as an option. A very large percentage of the questions in the newgroups have to do with the fact that Excel does not calculate the way its users do. -- Kind regards, Niek Otten Microsoft MVP - Excel "Dave Peterson" wrote in message ... And if you really cared about this kind of accuracy, why would you be using excel--a general purpose spreadsheet program??? Jim Thomlinson wrote: Now that every one else has waded in I will ask the question... Why would you need more than 15 digits of accuracy? With that many digits I can get the distance from here to the sun to less than a millimeter. Just curious. -- HTH... Jim Thomlinson "brianpo" wrote: Multiplying 111111111 by 111111111 in Excel 2007 results in 12345678987654300, which is obviously wrong. Is there a way to improve the accuracy of results over 15 digits? Thanks, Brian -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
Exactly!
That's why I plea for Decimal Arithmetic. That's the way we calculate. And it is not difficult to implement (we teach it to children, don't we?) in hardware or software. And we implement it in calculators, like your example. Performance isn't really an issue, I'm sure. We regain all the lost productivity (because of not understanding how Excel calculates) in no time. -- Kind regards, Niek Otten Microsoft MVP - Excel "Dave Peterson" wrote in message ... I remember trying to explain to someone why their average golf scores (with handicap??) that were calculated in excel didn't match what they got with their $2 calculator. I kept getting a "yeah, but my calculator can do it!" response. Niek Otten wrote: < And if you really cared about this kind of accuracy, why would you be using excel--a general purpose spreadsheet program??? Well, sometimes people just don't know. There was a time I didn't know about the "limited" precision. I'm in the financial business and I've never found span of precision a problem. But I did have problems with the fact that some numbers can not be represented exactly. If you round to (for example) a whole number, two calculation methods can lead to two totally different results. For example one calulation method results in 15.4444444444449, the other one yields 15.5000000000001. Then one rounds to 15, the other one rounds to 16. But there are sometimes unexpected effects. Take the example of the distance to the sun. What if it were not the distance but the angle you had to fire a rocket? For some interesting examples, look he http://www.eecs.berkeley.edu/~wkahan/Mind1ess.pdf In the newsgroups, two behaviors if Excel are often confused. One is that the number shown may not be the actual value (the "penny-off" problem), the other is the fact that not all numbers can be represented exactly. That last group is often confused because binary representation and floating-point computation are often not treated as separate issues. In fact, binary representations can mimic all decimal calculations, as long as you don't try to save on the number of bits. Good examples, sometimes even in hardware, can be found in BCD (Binary Coded Decimal) representaions, but that is certainly not the only method. Even in VBA, (so close to Excel!), you can find data types like Currency. I keep calling for decimal arithmatic in Excel, as an option. A very large percentage of the questions in the newgroups have to do with the fact that Excel does not calculate the way its users do. -- Kind regards, Niek Otten Microsoft MVP - Excel "Dave Peterson" wrote in message ... And if you really cared about this kind of accuracy, why would you be using excel--a general purpose spreadsheet program??? Jim Thomlinson wrote: Now that every one else has waded in I will ask the question... Why would you need more than 15 digits of accuracy? With that many digits I can get the distance from here to the sun to less than a millimeter. Just curious. -- HTH... Jim Thomlinson "brianpo" wrote: Multiplying 111111111 by 111111111 in Excel 2007 results in 12345678987654300, which is obviously wrong. Is there a way to improve the accuracy of results over 15 digits? Thanks, Brian -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
If it's simple things like this, you can get a text representation of
the answer: =Times(111111111, 111111111) =Times(111111111, 111111111, 2) Returns: 12,345,678,987,654,321.00 24,691,357,975,308,642.00 This is just the very simpler version... Function Times(ParamArray v()) Dim j As Long Times = CDec(1) For j = 0 To UBound(v) Times = Times * v(j) Next j If WorksheetFunction.Log10(Times) 15 Then Times = FormatNumber(Times, , , vbTrue) End If End Function = = = = = HTH :) Dana DeLouis brianpo wrote: Multiplying 111111111 by 111111111 in Excel 2007 results in 12345678987654300, which is obviously wrong. Is there a way to improve the accuracy of results over 15 digits? Thanks, Brian |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
On Aug 27, 7:02*am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: Now that every one else has waded in I will ask the question... Why would you need more than 15 digits of accuracy? With that many digits I can get the distance from here to the sun to less than a millimeter. Just curious. Hi Jim, Most of my customers don't use xlPrecison for numbers that represent any kind of physical measurement. Instead, they are mostly using numbers to represent abstract, non-physical measurements. Many of them are in finance. Others are doing pure mathematics -- mostly number theory, where as far as I can tell, the number doesn't represent anything other than itself and the patterns of digits within it. Others are doing things so unique I'm not sure how to categorize them. There may be a few delusional cranks among them, but most of them come across as highly intelligent and educated; certainly not less so than the chorus of dogmatists who insist there is no valid use for more than 15 significant digits of precision. In some cases it's not only about the precision but also about very large and very small numbers, far outside Excel's numeric range. Some of them just want exact results (no binary conversion errors) without dealing with the issues of Precision As Displayed or Round. Generally those customers are well aware of what the options are. BTW, here's an example of how precision can be a matter of life and death: During the Gulf War, a binary conversion error led to the deaths of 28 American soldiers (and around 100 injured) on February 25th, 1991 when an American Patriot missile failed to intercept an Iraqi SCUD missile headed toward their Army barracks. The error was caused by storing time in 1/10 second increments, with binary conversion errors on converting 0.1 to binary. Though each conversion error was tiny, the error accumulated enough to make the Patriot's navigation software miss the SCUD, which then reached its target. Also BTW, I'm curious -- how does 15 significant digits of precision get you the distance from here to the sun to less than a millimeter? Isn't the average distance approximately 149,600,000,000,000 millimeters, which would let you get to the *nearest* millimeter in 15 significant digits of precision (or within one millimeter, assuming truncation rather than rounding), but not to a fraction of a millimeter? Not that it really matters, of course. Greg Lovern http://precisioncalc.com |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
I would like to clarify the false impression that your Patriot Missile
example left. The problem was not binary math per se, but programming that was not appropriate to the hardware it was intended to run on. Programmers are expected to know the limitations of the intended hardware and effectively work around them. This unfortunately did not happen here. Details of the original incident are summarized at http://www.ima.umn.edu/~arnold/disasters/patriot.html The guidance system only performed an external time check when the missile battery was turned on. It interpreted the current time as the number of 10ths of a second since the system booted, which means that no arithmetic system would make up for errors in the internal clock. Since knowing the time was critical to the guidance system, defensive programming would have periodically verified the time externally. The Dutch Patriot batteries (using the same flawed software) were much more accurate because they rebooted frequently, which had the effect of forcing the time to be verified more often. Even if the clock was accurate, it is true that the decimal binary conversion would have an impact in the original algorithm, but the internal storage format had effectively less than single precision for the representation of 1/10. Had IEEE double precision (used by Excel) been employed, the time error (in 10ths of a second) after 100 hours of continuous operation would have been about 2E-11 instead of 0.3, and thus would have been completely negligible. Also, it did not take this tragic miss to uncover the problem, a programming patch had already been written and shipped; it just arrived one day too late. Jerry "Greg Lovern" wrote: On Aug 27, 7:02 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Now that every one else has waded in I will ask the question... Why would you need more than 15 digits of accuracy? With that many digits I can get the distance from here to the sun to less than a millimeter. Just curious. Hi Jim, Most of my customers don't use xlPrecison for numbers that represent any kind of physical measurement. Instead, they are mostly using numbers to represent abstract, non-physical measurements. Many of them are in finance. Others are doing pure mathematics -- mostly number theory, where as far as I can tell, the number doesn't represent anything other than itself and the patterns of digits within it. Others are doing things so unique I'm not sure how to categorize them. There may be a few delusional cranks among them, but most of them come across as highly intelligent and educated; certainly not less so than the chorus of dogmatists who insist there is no valid use for more than 15 significant digits of precision. In some cases it's not only about the precision but also about very large and very small numbers, far outside Excel's numeric range. Some of them just want exact results (no binary conversion errors) without dealing with the issues of Precision As Displayed or Round. Generally those customers are well aware of what the options are. BTW, here's an example of how precision can be a matter of life and death: During the Gulf War, a binary conversion error led to the deaths of 28 American soldiers (and around 100 injured) on February 25th, 1991 when an American Patriot missile failed to intercept an Iraqi SCUD missile headed toward their Army barracks. The error was caused by storing time in 1/10 second increments, with binary conversion errors on converting 0.1 to binary. Though each conversion error was tiny, the error accumulated enough to make the Patriot's navigation software miss the SCUD, which then reached its target. Also BTW, I'm curious -- how does 15 significant digits of precision get you the distance from here to the sun to less than a millimeter? Isn't the average distance approximately 149,600,000,000,000 millimeters, which would let you get to the *nearest* millimeter in 15 significant digits of precision (or within one millimeter, assuming truncation rather than rounding), but not to a fraction of a millimeter? Not that it really matters, of course. Greg Lovern http://precisioncalc.com . |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
Greg Lovern wrote: On Aug 27, 7:02 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Now that every one else has waded in I will ask the question... Why would you need more than 15 digits of accuracy? With that many digits I can get the distance from here to the sun to less than a millimeter. Just curious. Hi Jim, Most of my customers don't use xlPrecison for numbers that represent any kind of physical measurement. Instead, they are mostly using numbers to represent abstract, non-physical measurements. Many of them are in finance. Others are doing pure mathematics -- mostly number theory, where as far as I can tell, the number doesn't represent anything other than itself and the patterns of digits within it. Others are doing things so unique I'm not sure how to categorize them. There may be a few delusional cranks among them, but most of them come across as highly intelligent and educated; certainly not less so than the chorus of dogmatists who insist there is no valid use for more than 15 significant digits of precision. In some cases it's not only about the precision but also about very large and very small numbers, far outside Excel's numeric range. Some of them just want exact results (no binary conversion errors) without dealing with the issues of Precision As Displayed or Round. Generally those customers are well aware of what the options are. BTW, here's an example of how precision can be a matter of life and death: During the Gulf War, a binary conversion error led to the deaths of 28 American soldiers (and around 100 injured) on February 25th, 1991 when an American Patriot missile failed to intercept an Iraqi SCUD missile headed toward their Army barracks. The error was caused by storing time in 1/10 second increments, with binary conversion errors on converting 0.1 to binary. Though each conversion error was tiny, the error accumulated enough to make the Patriot's navigation software miss the SCUD, which then reached its target. Also BTW, I'm curious -- how does 15 significant digits of precision get you the distance from here to the sun to less than a millimeter? Isn't the average distance approximately 149,600,000,000,000 millimeters, which would let you get to the *nearest* millimeter in 15 significant digits of precision (or within one millimeter, assuming truncation rather than rounding), but not to a fraction of a millimeter? Not that it really matters, of course. Greg Lovern http://precisioncalc.com |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Improve Excel accuracy over 15 digits
Greg Lovern wrote: On Aug 27, 7:02 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Now that every one else has waded in I will ask the question... Why would you need more than 15 digits of accuracy? With that many digits I can get the distance from here to the sun to less than a millimeter. Just curious. Hi Jim, Also BTW, I'm curious -- how does 15 significant digits of precision get you the distance from here to the sun to less than a millimeter? Isn't the average distance approximately 149,600,000,000,000 millimeters, which would let you get to the *nearest* millimeter in 15 significant digits of precision (or within one millimeter, assuming truncation rather than rounding), but not to a fraction of a millimeter? Not that it really matters, of course. Simple, since 15 significant digits of precision can give you the distance to the sun to the nearest millimeter the error will be LESS than one millimeter. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel time format / accuracy | New Users to Excel | |||
how can i improve my skills in excel | Excel Worksheet Functions | |||
improve help samples in excel 2003 | Excel Discussion (Misc queries) | |||
How do I improve Accuracy of trendlines? | Charts and Charting in Excel | |||
How can I set the tab to improve navigation in Excel? | Setting up and Configuration of Excel |