Skip to main content
deleted 896 characters in body
Source Link
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 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)

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

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)

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)
added 37 characters in body
Source Link
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 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)

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)

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

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:

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)

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

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)
added 1298 characters in body
Source Link
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 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)

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:

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)

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 as a string but would require significant math magic.

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

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:

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)
added 53 characters in body
Source Link
CodeCamper
  • 867
  • 5
  • 7
Loading
added 232 characters in body
Source Link
CodeCamper
  • 867
  • 5
  • 7
Loading
Source Link
CodeCamper
  • 867
  • 5
  • 7
Loading