[Solved] Inaccuracy for big power function

[Lu5ck]

There seems to be inaccuracy when the numbers become too big, more than 15 digits. For example, 12^18 will gives 26623333280885200000 which is of course inaccurate.

Is there any ways to make this calculation accurate?

[Hagar Delest]

Hi and welcome to the forum!

Spreadsheets are not designed for such calculation I think. Use a dedicated software.
It may be related to the floating point calculation method used in computers.

Please add '[Solved]' at the beginning of your first post title (edit button) if your issue has been fixed.

[RoryOF]

You might find useful information on spreadsheet accuracy in this paper
http://www.jstatsoft.org/v34/i04/paper

[jrkrideau]

You might find useful information on spreadsheet accuracy in this paper
http://www.jstatsoft.org/v34/i04/paper

Thanks Rory, I had not seen that one.

[jrkrideau]

There seems to be inaccuracy when the numbers become too big, more than 15 digits. For example, 12^18 will gives 26623333280885200000 which is of course inaccurate.

Is there any ways to make this calculation accurate?

Not with a spreadsheet. Below is an extract from the R FAQ . The issues apply to a spreadsheet such as AOO Calc as well. For more information than you are ever likely to want to know follow up the Goldberg paper linked in the extract.

I believe that Mathematica or Maple will handle arbitrarily large numbers. I think that there also is some OpenSource software that will slso do so but I don't have a link.

Code: Select all

7.31 Why doesn't R think these numbers are equal?

The only numbers that can be represented exactly in R's numeric type are integers and fractions whose denominator is a power of 2. Other numbers have to be rounded to (typically) 53 binary digits accuracy. As a result, two floating point numbers will not reliably be equal unless they have been computed by the same algorithm, and not always even then. For example

     R> a <- sqrt(2)
     R> a * a == 2
     [1] FALSE
     R> a * a - 2
     [1] 4.440892e-16

The function all.equal() compares two objects using a numeric tolerance of .Machine$double.eps ^ 0.5. If you want much greater accuracy than this you will need to consider error propagation carefully.

For more information, see e.g. David Goldberg (1991), “What Every Computer Scientist Should Know About Floating-Point Arithmetic”, ACM Computing Surveys, 23/1, 5–48, also available via http://www.validlab.com/goldberg/paper.pdf. 

[acknak]

... I think that there also is some OpenSource software that will slso do so but I don't have a link. ...

Emacs "calc" mode--for one:

12
18
^ 26623333280885243904
12
38
^ 102067469997853225734913580209377959215104

I'm no mathematician, but it seems a little unfair to compare sqrt(2)^2 to 2: isn't that impossible to compute with a finite calculator?

[MrProgrammer]

I'm no mathematician, but it seems a little unfair to compare sqrt(2)^2 to 2: isn't that impossible to compute with a finite calculator?

All modern floating point systems store numbers as rational fractions where the denominator is a power of 2. It has been known for at least 2500 years that √2 is not equal to any rational fraction, that is, it's an irrational number (see below). So √2 certainly can't be represented as one where the denominator is a power of 2, no matter how many bits we are allowed to use.

Unfortunately for the OP, puny Windoze has no built-in tools for high precision calculations like Unix/Linux/Mac have. But there's certainly software available for Windoze, some of it free. A web search would quickly find numerous references. A spreadsheet is definitely the wrong tool for high precision calculations. Just because you could perform certain calculations in a spreadsheet doesn't you should.

Proof:

1. Assume that √2 is a rational number, meaning that there exists an integer C and an integer D such that C/D = √2.
2. If C and D share any common factors, cancel them so that we have A/B = √2 where A and B share no common factor.
3. It follows that A²/B² = 2, so A² = 2B2².
4. Therefore A² is even because it is equal to twice the integer B².
5. It follows that A must be even (as squares of odd integers are never even).
6. Because A is even, there exists an integer K that fulfills: A = 2K.
7. Substituting 2K from step 6 for A in the second equation of step 3: (2K)² = 2B², so 4K² = 2B² and 2K² = B².
8. Therefore B² is even because it is equal to twice the integer K², and so B must also be even.
9. By steps 5 and 8, A and B are both even, which contradicts that they share no common factor.
10. Our assumption that we have integers with C/D = √2 must be incorrect, hence √2 is irrational.

The discovery that √2 was an irrational number was a mathematical secret 2500 years ago. The punishment for divulging the secret was death.

[Jan_J]

Great number of popular software deal only with floating point arithmetcs. So is in the case of Calc. The double precision data type has only 15 to 16 decimal numbers; all the rest is scaling, being a kind of “digital zooming”, informally speaking. But this is enough for many practical purposes.
In the Open Source world there is still a number of software that can keep any precision of integer arithmetics. E.g. Python has this feature, as long as you deal with integers. It has also a link to gmp -- the Gnu Arbitrary Precision library that allows you to declare your own number of digits in floating point calculations -- despite the fact that Python's builtin float data type is double precision type.
Maxima is Open Source symbolic calculus toolbox. It generally handles rational arithmetics that can be converted to any precision decimals.