# Set calculation precision in Google Sheets

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? Dec 10, 2019 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? Dec 10, 2019 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 Dec 10, 2019 at 12:53
• fyi libre office has the exact same problem Dec 10, 2019 at 12:54
• @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... Dec 10, 2019 at 23:11

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)
``````
• @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. Dec 10, 2019 at 23:04

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.  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