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?
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)
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
Why does Google Spreadsheets says Zero is not equals Zero? (Web Applications)
Google Sheets not processing very large numbers (Web Applications)
More Precise Floating point Data Types than double? (Stack Overflow)
How to deal with floating point number precision in JavaScript? (Stack Overflow)
