Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    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>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    5 Star Lounger
    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>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    5 Star Lounger
    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 Arithmetic-Geometric 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>

Posting Permissions

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