Getting a 200-digit accurate multiplication result in Excel

Topic

New Reply

[PKCano]

I like interesting numbers and I love using Excel. It all started with this interesting 20 digit number:
12157692622039623539
Take each digit and raise it to the power n, where n is the position from left to right. So, first digit is 1, raised to power 1, second digit is 2, raised to power 2, ..fifth digit is 7, raised to power 5 etc etc all the way to the 20th digit 9, raised to the power of 20. Then add them all up. And the answer you get is..
12157692622039623539

I wanted to check this out and verify it in Excel, but had trouble with the last digit 9, raised to the power of 20. Excel only calculates to 15 digits precision, so 9^20 was short by 28801. This 28801 is another interesting number, because I think it’s the zip code for Ashville in North Carolina USA. Maybe our Retired Geek can confirm that? Anyway, I was fed up with Excel’s default 15 digit precision, so I wanted to create a worksheet that would increase the precision when multiplying numbers. My initial thought was to increase the accuracy to 80 digits precision. I thought this would be sufficient accuracy, based on counting the number of atoms in the universe. I thought this was the biggest countable number you could get. Then my son asked me about measuring a circumference of a circle to be accurate to the nearest planck length. Hmmm. OK then. As you know, the planck length is the smallest measurable length you can have. It is very small. If you expanded a human hair to the size of the universe, a planck length would be the thickness of a human hair in that universe.
OK, back to the real world. How many digits would I need if I wanted to count the number of ‘planck pixels’ in the universe? If I start with the universe having a diameter of say, 94 billion light years, and a light year is 6 trillion miles, and a mile is 63,360 inches, and an inch has this many planck lengths, then I calculated I would need at least 185 digits to accurately count all the ‘planck pixels’ in the universe. To allow for any underestimate on my part, I increased this by a thousand million million, to get to 200 digits. This should now be enough for the largest countable number you can have.

So I now have an Excel spreadsheet that allows you to multiply two numbers to get up to 200 digits precision, without using any vba coding. There are some online big number calculators available, but I wanted to do this simply in Excel.
I hope you like my spreadsheet. I used Excel 2010 to keep it simple.
zeddy

• This topic was modified 1 year, 2 months ago by PKCano.
• This topic was modified 1 year, 2 months ago by zeddy. Reason: typo

Reply | Quote

Replies

Quotes that may apply:
“There are a lot of errors that happen in machines that go undetected,” Papadopoulos said. “Sometimes a machine just goes away and freezes. You always blame it on Microsoft. We do, too. It’s convenient. It’s convenient for Intel, too.”

“It’s a dirty secret. Floating-point arithmetic is wrong,” said John Gustafson, a principal investigator with Sun, based in Santa Clara, California. “It only takes two operations to see that computers make mistakes with fractions.”

1 user thanked author for this post.

zeddy

Reply | Quote

Hi

Just to be clear, I was fed up with the 15 digit precision limit. So instead of just complaining, I did something about it. I was able to solve my immediate calculation problem. Using Excel.

It’s still my favourite. And I love all the people that created it.

zeddy

 

Reply | Quote

Nice to see you about zeddy. 

Also nice to have some big numbers to play with.

cheers, Paul

1 user thanked author for this post.

zeddy

Reply | Quote

Thanks Paul

Sometimes when a computer misbehaves it helps if you switch it off completely, and then start it up again. Same thing happened to me last year. No problem. What they don’t tell you is the scorch marks around your heart from the zap from the paddles takes a long time to wear off.

I now always take more care to close down computers properly. No more just flicking the mains plug off.

zeddy

Reply | Quote

Playing with it in LO 7.5.7.1 and the conditional formatting doesn’t catch putting an “e” in the number. It does complain about the output – big redness.
Does it think I am playing with exponents?

Other oddities, but more on that tomorrow.

cheers, Paul

Reply | Quote

Ah thanks Paul

..I added a bit of conditional formatting as an afterthought.  Will have another look at it.

Really pleased to have you looking at it.

zeddy

 

Reply | Quote

Zeddy,

You are correct! 28801 is one of the zip codes for Ashville NC.

Good to see you active here! Hope to see more from you..

Your Friend,

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

zeddy

Reply | Quote

Hi RG

As you know, you can prepare an Excel cell to display numeric values only.
You can also choose to display zero decimals.
You also have to make the cell big enough to display what you want.
If you did this, and then tried to enter any number longer than 15 digits, Excel will only allow your first 15 digits, and then put zeros for all the other digits you enter.
So, for 9^20 when you try to enter this correct value into a cell
12157665459056928801
..Excel shows this..
12157665459056900000

(Thank you Ashville for reminding me of this Excel discrepancy).

The simplest way I could deal with this was to display my large number input values displayed as text format, and then use other cell formulas to extract digits for the calculation.
Then use other formulas to calculate the correct result and then display the large number result back as a text formatted cell.

If Excel had a feature to allow the User to choose their own desired precision, we wouldn’t need to make such a work-around.

Thanks again RG and you are indeed truly a friend.

zeddy

 

Reply | Quote

If I enter a value (‘123456789098767554433) into the second field it goes red and refuses to work. Nope, scratch that. It’s a bit weird. Once it fails, any number over 16 digits also fails. Put a small number in the cell to fix it.

Adding a single quote to the front of a number sometimes results on the quote showing in the cell and being added to your number.
I can reproduce it consistently by copying one of your 100 digit numbers, not including the quote, then selecting the cell, typing quote and pressing Ctrl V. The number of digits cell shows 101, but this only happens in the “n2” cell.
I’ve seen it in n1 but can’t reproduce.

I am using LO, so Excel may not be a problem.

cheers, Paul

1 user thanked author for this post.

zeddy

Reply | Quote

Dropping one digit from the input (press Backspace) fixes the 101 digits, but it shows the quote and fails to calculate.

cheers, Paul

Reply | Quote

Hi Paul
The character displayed on my screen, from that value in your post, is NOT a quote mark but is a grave accent mark. Don’t get me started. A long time ago I had Graves’ disease. It is an autoimmune disorder. My thyroid was surgically removed. And then my spleen. And then a lung. Heart op later this year. Playing with Excel keeps me fit.

So the issue might be with LO, which I don’t have.
My posted file was intended to deal with digits only, so no provision for decimals or scientific notation exponent E values.

In Excel we can use built-in CLEAN and TRIM functions to tidy-up for unwanted non-printable characters and extra spaces etc etc. I could also use a formula to detect for any other non-digit characters in any language or any character-set, but I didn’t.

I just wanted to be able to get the largest countable number you could EVER get. So you don’t need to know pi to a million digits to get a real-world accurate measurement to the nearest proton etc etc. It’s OK to ask why there are a lot more 5’s than expected in that first million decimal digits of pi ( and why there is such an unexpected shortage of 6’s???) but that is in the math world not the real physical world. I like Excel and I really like this Forum.

Thank you so much for your input Paul. You are always very helpful.
I could make some improvements to my spreadsheet if anyone needs it!
zeddy

 

Reply | Quote

zeddy wrote:

NOT a quote mark but is a grave accent mark

That is the forum software. Try this. '123456789098767554433

Unfortunately I don’t have Excel to do a back to back comparison. More investigation in LO required!

cheers, Paul

1 user thanked author for this post.

zeddy

Reply | Quote

Hi Paul
I have attached my v2 version of my file, which allows big multiplication of two numbers as long as the total number of digits used is under 204.

This version now allows you to multiply two numbers, which may now include decimal values, with better than 15 digit accuracy in the result.

I tried this file out in a downloaded LibreOffice 7.6.5.2 and it seemed OK.
In LO, it seems you can enter your big numbers n1 and n2 in the specified input cells in the sample file without needing to precede them with an apostrophe – perhaps because I had already formatted those input cells as Text cells. So when copy-pasting into those cells, numbers only are OK.

I hadn’t intended to do much User input validation for the n1 and n2 numbers. I was more interested in getting the accuracy precision beyond Excel’s 15 digit restriction. However, I added a couple of simple tests, like checking you mustn’t enter more than 1 decimal point in a number, cannot embed multiple minus chars or other non-digit chars. I also used TRIM and CLEAN standard Excel functions in the background calcs to deal with any unwanted spaces etc etc.

Thank you once again for showing an interest. Someone might find this useful.
zeddy

Reply | Quote

Works nicely in LO with the new text format rather than number. (What is @ as a format?)
No longer need to add an apostrophe so no issue with long numbers.

In the cells formatted as text I still get long numbers converted to exponents if I don’t use an apostrophe, which is the same as Excel, although it takes 17 digits to convert in LO.

How does Excel fare with the cells formatted as text?

cheers, Paul

Reply | Quote

Hi Paul
The @ character is available as a formatting code for cell display in Excel.

Specifically, in Excel you can use
Format Cells>Number>Custom
..then scroll down to the bottom of the custom codes list and you will find it.

As far as I know, the @ formatting code just means treat everything as text.

In my enthusiasm for posting my previous updated Excel file with the ability to include big numbers with decimals, I didn’t test for entries which are ENTIRELY decimal values. I had to make a minor adjustment in my formula which works out where to put the decimal point in the answer. This now works correctly when you enter numbers starting with a decimal point, with or without a leading zero. My attached version 2c deals with this.

So we now have a spreadsheet which improves precision from 15 digits to 204.
If you improve precision by just 6 digits, does that count as ‘a million times more accurate’??? ..in any case, my method is at least 10 times more precise for big number multiplication!
zeddy

 

Reply | Quote

Hi Zeddy,

 

After a longggg break, got your post. It is great to learn from you.

Hope you are doing great. Take care.

Best Regards

Shazzad

Reply | Quote

Hi Shazzad

I’m still in agony from my long break. Actually, it was crushed shoulder blade and multiple fractures in my upper right arm. And a cracked head. Still wearing a sling after 5 weeks. good news is my 4 broken bones are nicely lined up so no surgery needed. And specialist said my head CT Scan didn’t show anything. Gotta laugh at that! All because I tripped over a cable and crashed violently into my concrete garage floor.

So stay safe and keep out of trouble. I am learning to type left-handed in Excel. Bit slow. But safer than being outside.

zeddy

 

Reply | Quote