Skip to main content
6 of 6
deleted 896 characters in body
CodeCamper
  • 867
  • 5
  • 7

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)
CodeCamper
  • 867
  • 5
  • 7