Thread: Number Formatting to Significative Digits (XL 2000)

1. Number Formatting to Significative Digits (XL 2000)

Hi,

Is there a way to give a cell a custom format such that it will display as many digits (not decimal places) as the number of significative digits Excel works with? (I don't know that number either). For example, if excel worked with 16+2 significative digits, a number like 724.16 would show as 724.1600000000000; 3 would look like 3.000000000000000; and so on, but I wouldn't visually miss any digit.

I hope I've been clear enough. Thank you in advance.

2. Re: Number Formatting to Significative Digits (XL 2000)

You could use scientific notation with 14 decimals. The integer part plus the 14 decimals provide the 15 significant digits Excel works with.

3. Re: Number Formatting to Significative Digits (XL 2000)

Thank you, Hans.

I hadn't even considered scientific notation which is most common when working in this fashion.

4. Re: Number Formatting to Significative Digits (XL 2000)

Just because excel uses 15 digits for all its calculation, does not make them all significant.

Personally, I see no need for having to maintain more than 3 or 4 significant figures in a final answer. Fifteen significant digits really seems like overkill.

As an example in the precision required, it only requires 14 significant digits to report the distance from the earth to the sun to the nearest sixteenth of an inch.

I wrote a "discussion" of Thoughts and Ideas on Significant Digits in Excel on Allen Wyatt's Excel Tips pages if you are interested.

Steve

5. Re: Number Formatting to Significative Digits (XL 2000)

Hi Steve,

Thanks for your article. It's been a long time since I took the Numerical Analysis course and it was a good refresher, with some new stuff also.
I know I'll very rarely need to have Excel display a number with all its significant digits. But I happened to think "and what if I ever do?". That was enough to get my curiosity on. I think it must be a very common thing among Loungers to waste hours just to see if something can be done. OR resource to other Loungers when enough time has been wasted <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

In this case, I don't know why, I was playing around with this Arithmetic-Geometric Mean sequence when I thought, go figure why, of posting the question. See attached. Man, does it converge fast.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•