Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Turn off rounding (2003)

    How can I turn off rounding of numbers greater than 15 digits?

    Thanks,

    itconc

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Turn off rounding (2003)

    Excel only uses 15 significant digits, so numbers with more than 15 digits will always be rounded.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Turn off rounding (2003)

    Thanks Hans. I areciate your fast response.

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Turn off rounding (2003)

    If you need to perform calculations with more than 15 significant digits there are a couple of add-ins that may assist, xlPrecision and XNumbers.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn off rounding (2003)

    Could you be more specific about what your problem is? There may be other ways around this. For example, if you are dealing with long numbers that are not involved in calculations (for example, part numbers), you can enter them as text and have more than 15 digits.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Turn off rounding (2003)

    They are numbers and the user wants to do calculations with them. After reading the reply from Hans, I recommended dividing the numbers by 1 mio or 100,000 depending on how large the numbers are. With a note in the spreadsheet to the effect that the numbers are in millions (or whatever), that should work. I also recommended investing in the xlPrecision add-in Tony recommended.

    Thanks,

    itconc

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn off rounding (2003)

    Dividing the numbers by 100,000 or 1,000,000 will not be any different than having Excel round to 15 digits, Excel will still only work with 15 digits. That will just affect where the decimal is placed. If you have to do calculations with numbers greater than 15 digits, the only solution is something like the addin that Tony recommended.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Turn off rounding (2003)

    Well, when I divide 1,500,000,000,000,000 by 100000 I can enter/show the decimals. That will take care of the problem. It would be easier to enter the numbers to begin with by dividing them by some number. Most large companies show their financial data in millions or thousands or whatever. That is what I am talking about here. Seems to work fine for us. The add-in is obviously a better permanent answer.
    Thanks,

    itconc

  9. #9
    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: Turn off rounding (2003)

    But after 15 digits they will still all be zero whether you divide or not. Doing division also increases the chances of rounding errors creeping into the numbers

    Steve

  10. #10
    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: Turn off rounding (2003)

    I am a little curious. Why do you require so many significant figures? What type of analysis are you doing?

    It seems like a lot of "noise" to me, once you get past 4 or 5. Just 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.

    You can do monetary transactions to the nearest cent as long as you are under 10 trillion dollars and keep only 15 sig figs.

    Steve

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn off rounding (2003)

    <hr>
    Why do you require so many significant figures?
    <hr>

    Analyzing waste in the government budget? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  12. #12
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Turn off rounding (2003)

    Ha, Ha..
    Actually, I work for a large oil company. We have lots of engineers who think they need to push the program to new limits. I get their questions and IT problems!
    Thanks all of you for your help.

    itconc

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn off rounding (2003)

    Tell them that if they drill that deep they will come put the other side!
    Legare Coleman

  14. #14
    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: Turn off rounding (2003)

    It tells me the engineers don't understand precision or significant figures. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Reminds me of the "bean counting" accountants who always "balanced" the inventories of material in and material out for every month to the pound. What always amazed me is that it always balanced, even though I knew that production took samples of material (in process and finished goods) at various intervals during the day and this material lost (hundreds of pounds a month for many products). These "lost pounds" never seemed to affect their calculations at all.

    Steve

Posting Permissions

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