Replies

..or even less typing in cell F8

=E8/B2

zeddy

1 user thanked author for this post.

WSJon5

How about in cell F8

=IF($B8>0,E8/B2,"")

..less typing

zeddy

1 user thanked author for this post.

WSJon5

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

 

Don’t let the Predator win.

zeddy

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

 

Thus reminded me of getting a paint job done.

zeddy

4 users thanked author for this post.

wavy, SueW, Cybertooth, RetiredGeek

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

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

 

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

 

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

 

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

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

 

Hi Oscar

I wanted to thank you again for this interesting topic. It’s amazing how fast things have changed and how useful and accessible modern technology is, especially with GPS.

OscarCP: “If I understand this correctly, you are referring to the LIGO setup”

I don’t have the LIGO budget. My budget is more like a LEGO setup. The twin-laser setup is to destructively cancel ‘local-effects’, they are also counter-rotating and circular-polarized, one is RHCP and the other is LHCP. This is to deal with signal-chirality issues (the target-image-reflection cannot be superimposed by any geometric combinations of rotations, lateral translations etc etc).

But never mind all that, within 4-inches is definitely accurate enough for me for measuring mountain heights. Not so for my thoracic surgeon – that’s the size of a spleen (I wish I still had mine).

As for sea-level height, is that with or without all the sea sponges taken out? (I tried to calculate that some years ago, please refer to previous Lounge postings).

It’s a busy travel travel time ahead for me. The faster I travel, it seems the less energy I have. I must’ve had tachyons for breakfast again.

zeddy

1 user thanked author for this post.

OscarCP

Hi Oscar
When I first saw this post about Everest, it reminded me of my favourite question..
“What was the highest mountain on Earth before Mount Everest was discovered?”***

Now, as for measuring to “better than four inches” that sounds like the back of a cigarette packet, so maybe using a twin-laser-interferometer would get you within say, 10 hydrogen-atoms. That’s much less than a cat’s whisker.

zeddy

Hi Shazzad

Well it seems I’m back in circulation (excuse my phlebotomy pun).

I was thinking your route-planning question could be considered something like a railway-network with your delivery-destinations as ‘train stations’.  You could schedule your lorries as the ‘trains’. Note that some trains could be considered as ‘express trains’ i.e. not stopping at ALL stations etc etc etc.

So your distance-matrix would be something like a train-route-map. Just saying.

zeddy