7

This:

3.1415926535

produces:

3.141592654

How can I have it output the whole number as is?

I was using this with TO_TEXT(), getting the same result, but I guess the problem lies prior to that. The default setting seems to be to round numbers to the number of decimals in 3.141592654 (9).

I will stress that I cannot use TEXT() to format numbers explicitly, because the source data (coming in from 3rd party) appears over many lines (10000+) and I just need to reference the exact input values as is.

The "right way" to do this would be to set PLAIN TEXT format on the cell. However with existing value like 3.1415926535, that would end up with loss of data because the output will be 3.141592654. This is not acceptable for 10000+ rows.

Improve this question

2 Answers 2

Reset to default
13

The solution is quite simple. Press this button to increase the number of decimals: enter image description here

Now it will show the correct amount of decimals:
enter image description here

Please be aware that this works only up to 15 digits. Beyond that, Google Spreadsheets will ignore the rest.

Improve this answer
2
  • That's fine, but I think I need to edit this setting on a file level - the contents in the same column are randomly text and numbers. Current workflow also is to replace a sheet completely by adding a new one, and deleting the old one. I reference this source data sheet from another one by the name of the sheet. With your suggestion, I would have to remember to press that button, having that column selected, everytime I import new data/new sheet.
    – Jonny
    Mar 19, 2015 at 4:14
  • I am sorry but the explanation makes no sense to me. I have a cell set to # format and to 8 decimal places. I enter the first # below and Google Sheets continually changes it to the second # rounding back to 6 decimal places then 0's after that? 374,999,173.66771734 - 374,999,173.66771700 I also try in the next column =round(F3, 8) and the function works for 6 decimals and below but above 6 just shows 0's not the true #? You said above "Please be aware that this works only up to 15 digits. Beyond that, Google Spreadsheets will ignore the rest." I would be very happy if it did in fact work this
    – Ronald Russo Jr.
    Sep 23, 2018 at 14:14
0

If you are working in a field where that degree of decimal precision is important, then perhaps Google Sheets (where functional changes happen regularly, and many are not notified to the users) is not the best tool to use.

All applications round numbers at some point. If you need engineering precision in how this is done, then you may need to investigate applications that give you more control.

Improve this answer
1
  • I'm downvoting this because, like any statistical package, Google Sheets does not round numbers to the same imprecision as what is displayed - the number that is stored is more precise.
    – ahorn
    Jan 9, 2020 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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