Calculating Huge Numbers in Excel

In summary, the conversation discusses the limitations of Excel in performing calculations with large numbers. It is explained that the answer is constrained by the number of bits used by the computer, resulting in a dropped value as a rounding error. Other programs such as Computer Algebra Systems, calculator programs, and some programming languages can handle larger numbers. It is also mentioned that sometimes placing the smaller number first can prevent loss of precision in calculations with a large range of magnitude difference between two operands.
  • #1
StevieTNZ
1,933
878
Hi there,

If I perform the following calculation in Excel:
=100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000+50

I get: 100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000

Shouldn't the answer be:
100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,050?
 
Computer science news on Phys.org
  • #2
No, the answer will be constrained by the number of bits used by the computer to represent the numbers, and you've got WAY more than it can handle, so the 50 gets dropped as a rounding error.
 
  • #4
Is there any other program out there that will present the result given in my original post?
 
  • #5
Lots of other programs can do that.
Computer Algebra Systems, like Mathematica, Sage, Maxima, Reduce, Maple... all do that.
http://en.wikipedia.org/wiki/Computer_algebra_system

Some calculator programs, like bc and many others do that.
https://www.google.com/search?q=big+integer+calculator&oq=big+integer+calculator

Many programming languages, like Icon, Java... do that, don't be fooled by those who think 64 bits is big.
https://www.google.com/search?q=programming+language+big+integer&oq=programming+language+big+integer
 
  • #6
64 bits is chump change for deep calculations. You need a monster computer to process calculations beyond a few hundred decimal points. That is why numerical analysis takes so long and is so incredibly expensive.
 
  • #7
Just for grins, try putting the smaller number first.
 
  • #8
harborsparrow said:
Just for grins, try putting the smaller number first.

Do you really think that's going to have any effect on the fact that the larger number is WAY bigger than Excel can handle as an integer?
 
  • #9
phinds said:
Do you really think that's going to have any effect on the fact that the larger number is WAY bigger than Excel can handle as an integer?

Sometimes it does help to prevent loss of precision by putting the smaller number first, when there is a huge range of magnitude difference between two operands.
 
  • #10
harborsparrow said:
Sometimes it does help to prevent loss of precision by putting the smaller number first, when there is a huge range of magnitude difference between two operands.

Yes, and that does not answer my question at all. I am asking about a specific case.
 

1. How do I calculate large numbers in Excel?

To calculate large numbers in Excel, you can use the built-in functions such as SUM, PRODUCT, and POWER. These functions can handle numbers up to 15 digits long. If you need to work with numbers longer than 15 digits, you can use the scientific notation format or enable the "Precision as Displayed" option in Excel's settings.

2. Can Excel handle numbers with more than 15 digits?

Yes, Excel can handle numbers with more than 15 digits as long as the "Precision as Displayed" option is enabled in Excel's settings. This option allows Excel to display numbers with up to 30 decimal places, but it may affect the accuracy of calculations.

3. How can I format large numbers in Excel?

You can format large numbers in Excel by using the built-in number formatting options. These options allow you to display numbers in scientific notation, as fractions, or with custom number formats. You can also use the "Precision as Displayed" option to format numbers with more than 15 digits.

4. What is the maximum number of digits that can be displayed in Excel?

The maximum number of digits that can be displayed in Excel is 15. However, with the "Precision as Displayed" option enabled, you can display up to 30 decimal places. Keep in mind that this option may affect the accuracy of calculations.

5. How do I deal with rounding errors when working with large numbers in Excel?

To deal with rounding errors when working with large numbers in Excel, you can use the ROUND function to round your calculations to a specific number of decimal places. You can also enable the "Precision as Displayed" option, but keep in mind that this may affect the accuracy of your calculations. It is also important to double-check your formulas and make sure they are correctly referencing the cells with large numbers.

Similar threads

  • Computing and Technology
Replies
10
Views
1K
  • Computing and Technology
Replies
3
Views
2K
  • Computing and Technology
Replies
3
Views
835
  • Computing and Technology
Replies
5
Views
1K
Replies
9
Views
1K
  • Computing and Technology
Replies
19
Views
2K
  • Computing and Technology
Replies
5
Views
1K
  • Computing and Technology
Replies
31
Views
3K
  • Computing and Technology
Replies
4
Views
1K
  • Computing and Technology
Replies
14
Views
2K
Back
Top