+ Reply to Thread
Results 1 to 3 of 3

Why General format doesn't show more decimal places/significant digits?

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Why General format doesn't show more decimal places/significant digits?

    Example:
    A1=11978370000
    A2=12171257
    A3=A1/A2

    A3 is GENERAL format.

    You can double the width even 90 or 160 but it doesn't display anything better than
    984.1522531 (10 significant digits) (FYI 7 decimal places here, but the 10 is the operative number).
    In other words, it doesn't display all 15 distinct significant digits, or even, let's say 14, because of the operation (division). They're "there"; if you manually increase the number of displayed digits (icon: the little left arrow and .0 over .00) it will work its way up to
    984.1522531321130000... (the 15 sig digs I desired to see)(Yes, even though generally speaking only 14 are reliable here)

    In fact, the first time you increase decimals on A3, General literally changes to "Number format displaying 8 decimal places".
    984.15225313 (11 significant digits) (FYI 8 decimal places)

    This is not a question about IEEE floating point precision. This is not a question about the 15. This is strictly about General deciding to show 10 significant digits when 15 distinct are available. And yes, I say distinct, because obviously General shows 1 significant digit if you divide 10 by 20. (By the way, that is theoretically treated as 2 significant digits, 0 and 5, in some applications. Don't get hung up on that technicality.)

    It's as though General means "numeric, 10 digits; or less, if the number has less significant digits to display".
    This seems to be the case since long ago. Can that "10 or less for General" be changed? Is it always "10(or less)"? I never found this in Excel Options; is there a back door for this?

    It's disconcerting that I often will double the cell width to see if the the number is exactly {whatever is shown}, then undo, before using that number in a report or another workbook. For example I often use it as a "cheat" or crutch when I see something like 1.44 and I want to know if that number is really 1.44 "exactly" or is it 1.443. Now, that trick fails if there are 10 digits. You will see
    123456789.4
    when the actual number is
    123456789.44
    and it doesn't matter how wide you go. General just won't show that 44 cents. You'll have to change to Numeric and figure out how many decimal places to show before you decide "yes, it's 44 cents, not 44 and a half". That can be a tedious task. I'd like to see the max! Duh, right?

  2. #2
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    MA, USA
    MS-Off Ver
    365(PC) V:2412
    Posts
    1,477

    Re: Why General format doesn't show more decimal places/significant digits?

    is this just a rant or are you looking for a solution to the GENERAL format decimal situation?
    It seems like your fix is already stated and you just turn your workbook settings to Number format every time you want.
    or you can save a blank workbook with your number format settings as a template.
    see link below for instructions:

    https://stackoverflow.com/questions/...ormat-in-excel
    <----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.

  3. #3
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Re: Why General format doesn't show more decimal places/significant digits?

    I don't think you understand the question if you think that using replacing General format with a numeric one is a "fix", in so many regards, I don't know where I would start. But your expressive commentary and admonition are heard. I would spend time on re-explaining the post but the question is already a long one, and does ask a specific question, so I'll just leave it like this for anyone who knows the answer. Thanks for piping in though! Pipe in on any of my other threads when they appear!
    Chip, if you can read this, you're STILL making the world a better place with your immeasurably valuable gifts. Every week, you continue to help me (and untold numbers of others) tremendously. Practically no alternative outlets can match your effectiveness, even over the vast number of topics you demystify and empower usage of. You were, and still are, amazing.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Number of digits instead of decimal places
    By seanppp in forum Excel General
    Replies: 5
    Last Post: 04-24-2018, 04:45 AM
  2. [SOLVED] Return equivalent full value or significant digits after decimal point
    By mgcarino14 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-19-2016, 03:19 PM
  3. Format significant digits
    By Sloth in forum Excel General
    Replies: 7
    Last Post: 04-23-2014, 06:37 AM
  4. [SOLVED] Calculating decimal places in a non decimal format (ie sixes not tens)
    By Mike Brewer in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-07-2013, 08:58 AM
  5. How to show decimal position to two and then show them as only digits?
    By Ning in forum Access Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2012, 09:25 AM
  6. round down to 2 decimal places as a general rule
    By janper in forum Excel General
    Replies: 5
    Last Post: 06-13-2009, 12:01 PM
  7. format number of decimal places to show
    By maacmaac in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2009, 06:24 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1