Results 1 to 5 of 5

20050410, 19:29 #1
 Join Date
 Mar 2002
 Location
 Buenos Aires, Argentina
 Posts
 877
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.<img src=/w3timages/blue3line.gif width=33% height=2>
<img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

20050410, 19:37 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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.

20050410, 23:33 #3
 Join Date
 Mar 2002
 Location
 Buenos Aires, Argentina
 Posts
 877
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.<img src=/w3timages/blue3line.gif width=33% height=2>
<img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

20050411, 06:41 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20050411, 12:06 #5
 Join Date
 Mar 2002
 Location
 Buenos Aires, Argentina
 Posts
 877
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 ArithmeticGeometric Mean sequence when I thought, go figure why, of posting the question. See attached. Man, does it converge fast.<img src=/w3timages/blue3line.gif width=33% height=2>
<img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>