Results 1 to 14 of 14
Thread: Turn off rounding (2003)

20060314, 16:52 #1
 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

20060314, 16:59 #2
 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.

20060314, 17:06 #3
 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.

20060314, 17:10 #4
 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 addins that may assist, xlPrecision and XNumbers.

20060314, 18:32 #5
 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

20060314, 18:40 #6
 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 addin Tony recommended.
Thanks,
itconc

20060314, 18:45 #7
 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

20060314, 19:03 #8
 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 addin is obviously a better permanent answer.
Thanks,
itconc

20060314, 19:14 #9
 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

20060314, 19:48 #10
 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

20060314, 19:50 #11
 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

20060314, 19:57 #12
 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

20060314, 20:05 #13
 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

20060314, 21:04 #14
 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