Skip to main content
2 of 6
added 232 characters in body
CodeCamper
  • 867
  • 5
  • 7

Even worse putting it all in one cell still evaluates to 0. =128^128+1-128^128

This is because Google Sheets is using something similar to double precision, otherwise mathematical calculations could easily halt the average computer. 128^128 is an awfully large number. Remember as of today the limitation of google sheets is 50k characters per cell so that would also limit your ability to do amazing calculations.

using the base function we can see the actual accuracy of numbers... =base(128^128+1-128^128,10,1) yields the following error:

Valid values are between 0 and 9.0072E+15 inclusive.

which is a fancy way of saying if you want to work with even whole numbers above 200 trillion with exact precision you shouldn't be using normal spreadsheet software.

A simple way to prove this is to do 200 trillion minus 200 trillon and 1

=200000000000000-200000000000001

This equals -1 but if you add one extra zero and make it quadrillion you will find it rounds unpredictably. The base function is telling us that in more complicated situations even a number such as 300 trillion can result in unpredictable rounding but that you can safely work with a number around 200 trillion or less and convert between base systems.

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.

CodeCamper
  • 867
  • 5
  • 7