Number Formatting to Significative Digits (XL 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

Thank you, Hans.

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

Reply | Quote

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

Reply | Quote

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

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.

Reply | Quote