4

I have an array of numbers with 16 decimal places. When I copy them into a Libre Office Calc spreadsheet, the numbers are rounded off to the 15th decimal place. I assume this is a result of the double/real datatype being limited in what it can store.

To be clear, this isn't a formatting issue. I've adjusted the cell formatting to display 16 decimal places, and it does. Digits past the 15th are rounded to zero, however, and any manual corrections I attempt to make to the extra zeros are immediately discarded.

Is there any solution for this? For example, can I change the data type from double/real to something like Libre Office Base's decimal?

Improve this question
3
  • 1
    It maybe that spread sheets are not the correct tool, for what you are doing. If you are doing high precision physics simulations (I assume that you are, if you need this level of precision), then you should be looking at python, with the arbitrary precision library. If you are doing stuff with money (also needs precision), then I thing that there is a currency type. Commented Oct 1, 2018 at 17:50
  • 1
    What are you modelling, that need this level of precision? Commented Oct 1, 2018 at 17:51
  • 3
    According to en.wikipedia.org/wiki/IEEE_754 binary double 64bit have a precision of 15.95 digit.if leading digit are not null, you have hit representation limit. Commented Oct 1, 2018 at 19:50

2 Answers 2

Reset to default
-1

You have to do two things:

1- Round off the numbers: To calculate with the rounded off numbers instead of the internal exact values:

Choose Tools menu >> Options >> LibreOffice Calc >> Calculate page. Mark the "Precision" as shown field and exit the dialog with OK.

enter image description here

2- Keep in mind that the decimal place for the first round after decimal is zero, so if you would like to adjust for 16 decimals, then your decimal place must be 15

enter image description here

Improve this answer
1
  • 1
    this won't help because IEEE-754 double precision doesn't have precision as high as 16 digits Commented Nov 13, 2022 at 5:35
-1

GNOME 'gnumeric' instead of LibreOffice Calc or Microsoft Excel let's you display more digits. It also has capa to work with Calc *.ods or Excel *.xls(x) files :-)
Be aware that the precision and thus the amount of correct digits still depends on the IEEE datatype in use, and with the standard 'double' version is limited to 16 significant digits in most ranges while only 15! in the binary ranges below a 'power of ten range border', e.g. [ 0.5 .. 1 ), [ 8 .. 10 ), [ 64 .. 100) ... If you can keep your data outside these ranges you are done.
If not the rarely used 'long double' version can help you out. It allows reliably storing 18 and in most ranges 19 sig. decimal digits as long as the absolute values stay between 1E-4932 and 1E+4932.
Looks as if the precision restriction doesn't hit at all ranges for the long version, thus you might have 19 digits safe between 1E-2 and 2^69 ( 590295810358705651700 ). recheck carefully before putting in production!
Be aware that Calc and Excel can't handle the additional precision and! will destroy it in files if saved with them.
[ edit 2022-05-29 ] add info i forgot: gnumeric requires Linux as OS, for Win there is an old ver. 1.12.17? available, but AFAIK no 'long'.
For the request from @G-Man...: I - tried to - keep my answer short, any internet search will lead you to gnumeric: http://www.gnumeric.org/, exploring from there you'll also find the development long version. Also I did! explicitely distinguish and explain how many digits are correct - from a decimal POV - with which version and in which ranges. An attempt to make it more clear: a cell value in a spreadsheet can either be accounted as a binary deputy / representand / approximation for a decimal value - decimal POV -, then the digits after the grid / granularity / precision capa of the bin datatype are wrong - I call them conversion artefacts, or as a bin value itself - binary POV, then all digits shown by gnumeric are correct, but the precision of in- and output is limited by the precision the available bits can hold.
An example for a difference between both POV's: accounting the 17'th digit of IEEE doubles. They are correct 'nearest' to the bin value, but IMHO not decimal correct as not all of their nearby 17-digit values can be represented with doubles, and thus the 17'th digit may be a little off from intendet. [ /edit ]

Improve this answer
1
  • 1
    (1) What is this “gnumeric”?  How does one get it?  (2) You realize that displaying more digits without displaying more correct digits is worse than no answer at all, right?  (3) What is this “long double version” of which you speak?  Is it actually a separate product  /  package (if so, how does one get it?) or is it just a setting (if so, how does one set it?)?  … … … … … … … … … … … … Please do not respond in comments; edit your answer to make it clearer and more complete. Commented May 28, 2022 at 15:47

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.