3

If I try to calculate large exponents in LibreOffice Calc or OpenOffice.org Calc, the results come out to be erroneous. See these two examples where I calculate 13^18,

Using LibreOffice Calc

enter image description here

Using OpenOffice.org Calc

enter image description here

The result is erroneous in both the cases, as we can find using Calculator (gcalctool).

enter image description here

All the above tests were done in Ubuntu. I can not comment on Excel in MS Office since I do not have access to it.

Now, using a calculator will be possible, but cumbersome. I need to fill up a table with more than five hundred values. I know that I can resort to Euler's theorem or Fermat's little theorem or corollaries of these, but actually I am going to use the aforementioned table to show numerical validity (and use) of these theorems.

Is there a way to coax the spreadsheet programs to give me correct results?

6

Is there a way to coax the spreadsheet programs to give me correct results?

Probably not.

Excel certainly, and I expect other spreadsheet programs (for Excel compatibility) use Floating Point numbers based on the 64bit IEEE format. This is implemented in hardware on most current CPUs and thus is fast (even a moderately sized spreadsheet can involve a lot of calculation).

But floating point is a finite sized representation, therefore will only represent a finite subset of real numbers. Everything else will either be approximated or lead to an error condition.

The details of the limitation are widely documented (eg. What Every Computer Scientist Should Know About Floating-Point Arithmetic) but these get technical quite quickly.

  • 1
    Now that there are higher precision formats, is implementation in hardware and thus higher speed is the only reason the spreadsheet programs use 64 bit IEEE formats? Why did not they think of providing an option to choose from hardware only fast calculation or software plus slower calculation? Hope I did ask a stupid question. – Masroor Jun 2 '13 at 16:46
  • 1
    The need for such large calculations is not vary common for the typical user of a spreadsheet. If you are dealing with formulas that large you typically move to more specialized software, like Matlab or Octive. – Scott Chamberlain Jun 2 '13 at 16:49
  • @ScottChamberlain Tried Octave, rather jump crashed to it. However, it also produced errors for large numbers. Octave does not directly support large numbers, please see here. Then tried sage. Worked like a charm for my case. By using GNU Multiprecision Library (GMP), Sage can handle very large numbers, even numbers with millions or billions of digits. Thanks goes to you for pointing me to the right direction. – Masroor Jun 3 '13 at 2:12
  • Also, forgot to tell, sage has a nice LaTeX interface, which makes it even more attractive. – Masroor Jun 3 '13 at 2:18
  • 1
    @MMA Do enough of the computers used for Excel have such hardware support (the article you reference only mentions SPARC and System/370: neither major desktop platforms)? Would enough users benefit from it? Or should resource be spent on SpreadSheet features that more users will benefit from? Welcome to the real world of not unlimited resources and lots of competing feature ideas. – Richard Jun 3 '13 at 10:02
1

If you are an Excel user, you could try xNumbers. This is a freeware add in you can get at Xnumbers for Excel

  • "the ability to calculate with up to 250 digits" - sounds good ! – Mawg Jan 10 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.