2

Google Sheets uses some sort of floating point arithmetic in its internal calculation, even if only integer numbers are in the calculation. For example:

A1: =128^128
A2: =A1+1
A3: =A2-A1

Then, A3 yields 0.

How can I set up Google Sheets so that A3 will result in 1?

  • May I ask... what is the relevance of the "office-online" tag? – Tedinoz Dec 10 '19 at 12:18
  • Not a solution, but have you read Funny (rounding?) errors when adding and/or Why does Google Spreadsheets says Zero is not equals Zero? – Tedinoz Dec 10 '19 at 12:27
  • office-online is tagged because i hope somebody with office365 know-how can tell me if this problem exists there as well – Andreas Petersson Dec 10 '19 at 12:53
  • 1
    fyi libre office has the exact same problem – Andreas Petersson Dec 10 '19 at 12:54
  • 1
    @AndreasPetersson I provide in my answer an example of math magic if you know the number you want to work with ahead of time such as 128^128 and you want to do a single computation such as in your question in which the result will be less than 200 trillion, in such a situation my answer will work, if the result is above 200 trillion you will have less precision but more precision than without it... – CodeCamper Dec 10 '19 at 23:11
0

Google Sheets hasn't a way to change the calculation precision. Just look at the options in File > Spreadsheet Settings

There isn't any option there for that.

General

Calculation

Perhaps using Google Apps Script could be an option but you should also be aware of handle this could not be simple if you aren't familiar with floating-point related data types and operations. Related

| improve this answer | |
0

With Google App Scripts you could write a script to handle large numbers but all the calculations would be done in App Scripts and not in the spreadsheet itself, additionally with a bunch of complicated formulas you could break the problem into pieces less than 200 trillion and then assemble it back together as a string but would require significant math magic.

For your specific problem here is some math magic which can help you get an answer... Set calculation precision in Google Sheets (the following process below will increase the calculation precision by removing common preceding digits)

The example below works if you know the number you want to work with ahead of time such as 128^128 and you want to do a single computation such as in your question in which the result will be less than 200 trillion, in such a situation my answer will work, if the result is above 200 trillion you will have less precision but more precision than without it

How can I set up Google Sheets so that A3 will result in 1? (by adding one helper cell and typing out the exact number you will easily get 1)

in cell A1 don't write 128^128 just write the whole number so it doesn't round (DO NOT USE EQUAL SIGN FOR A1 or A2):

528294531135665246352339784916516606518847326036121522127960709026673902556724859474417255887657187894674394993257128678882347559502685537250538978462939576908386683999005084168731517676426441053024232908211188404148028292751561738838396898767036476489538580897737998336

Then in cell B1 just add 1 to it by hand and enter the exact number

528294531135665246352339784916516606518847326036121522127960709026673902556724859474417255887657187894674394993257128678882347559502685537250538978462939576908386683999005084168731517676426441053024232908211188404148028292751561738838396898767036476489538580897737998337

If you are using a ridiculous number like this and want to find only a small difference of less than 200 trillion it will be trivial with the following formula:

in cell A4 we will grab the common digits as follows:

=arrayformula(match("F",if(split(REGEXREPLACE(E13&"","(\d)", "$1 ")," ")=split(REGEXREPLACE(E14&"","(\d)", "$1 ")," "),"T","F"),false))

Then in cell A3 you can get your answer with increased precision using this formula:

=mid(A1,A4,128^128)-mid(A2,A4,128^128)
| improve this answer | |
  • 1
    @Rubén ok, I updated the answer to include math magic to remove preceding common digits to "increase precision" and "A3 will result in 1". Obviously the most comprehensive answer is to implement BigInteger in Google App Scripts as mine only works if you know the two numbers such as in this question and the increased precision will only be in proportion to the amount of common preceding digits. – CodeCamper Dec 10 '19 at 23:04

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.