View previous topic :: View next topic 
Author 
Message 
Ed Guest

Posted: Sun Apr 27, 2003 4:00 pm Post subject: Decimal Places 


By default, when I type a number into a Calc cell, it appears rounded to two decimal places. I have found out how to change the maximum number of decimal places to a higher number, but doing this displays trailing zeros after all numbers.
Is there any way I can format cells so that a number is shown with as many decimal places as required, upto a specified maximum, without trailing zeros? This would be equivalent to 'General' in MS Excel. Also it would be nice if irational numbers such as pi were rounded to as many decimal places as will fit in the celll. 

Back to top 


tburcham General User
Joined: 17 Apr 2003 Posts: 36

Posted: Sun Apr 27, 2003 10:10 pm Post subject: Is this what you were looking for? 


Hi Ed,
Try this as a userdefined format:
#.################
Is this what you were looking for?
tburcham 

Back to top 


Ed Guest

Posted: Mon Apr 28, 2003 3:14 am Post subject: 


This is more like it, but what really wanted was a format which also rounds the number to fit in the cell, if the string with that many decimal places is too long. Maybe OOo Calc is not mature enough to offer that option yet. 

Back to top 


ftack Moderator
Joined: 27 Jan 2003 Posts: 3102 Location: Belgium

Posted: Mon Apr 28, 2003 3:31 am Post subject: 


Tools Options Spreadsheet Calculate: change Decimal places to 8 or more. Believe me, this is what you were looking for. It is a rather stupid default to have general format numbers displayed with only two decimals. 

Back to top 


Ed Guest

Posted: Mon Apr 28, 2003 5:22 am Post subject: 


This still causes the same problem, though. That is that numbers too long to fit in the cell are represented as ###, rather than rounded to fit the space. 

Back to top 


m3L Newbie
Joined: 08 Nov 2005 Posts: 1

Posted: Tue Nov 08, 2005 7:51 pm Post subject: 


Resurection a long dead post here but oh well,
I'm looking for something similiar, a format the will put in as many decimal places as needed, but no more than nessisary  the same as General, but with using '()' instead of '' for negative numbers and also commas as seperators, the closest ive come is:
#,###.######;(#,###.######)
this works ok, but these always puts in the '.' even when there are no decimals. ie '12' shows up as '12.' and even '0' shows up as '.'
oh. . . nevermind I just found it, i'll still post it here because I spent so much time looking for it:
#,##0.######;(#,##0.######)
but wait '12' is still showing up as '(12.)'
Also I'll agree with Ed rounding 'til a number fits is alot more useful than showing ### 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

Posted: Wed Nov 09, 2005 4:58 am Post subject: Re: Decimal Places 


Ed wrote: 
Is there any way I can format cells so that a number is shown with as many decimal places as required, upto a specified maximum, without trailing zeros? 
Curious. Programs do what programmers want them to do. I have an additional question, as I was digging around with this. I thought I'd play with strings to see what came up, and was surprised and so got no further. Entering Pi to include 20 decimals, I thought I'd try finding the TEXT() value from that, and then intended to play with that. But it rounded automatically to 4 decimal places. That is if A1 has 3.14159...(to 20 decimals), then B1, with =TEXT(A1) showed 3.1416. Why not the full extent of the number?
David. 

Back to top 


liv2cod Newbie
Joined: 09 Nov 2005 Posts: 2

Posted: Wed Nov 09, 2005 12:28 pm Post subject: 


I also find the number formatting pretty lacking. At our company we have data which spans a large dynamic range. Some columns have numbers in the range 0..10 right next to columns that are 0..10e12. The "general" format rounds many cells to "0" which is completely wrong behavior. The default decimal settings of 2 places is also troublesome. I think the people who came up with this formatting model are thinking of monetary values.
I'd also love to see an "engineering" model for scientific formats where exponents are held to multiples of three, i.e. 10^0, 10^3, 10^6. This is a natural way of expressing values that are in MHz, km, milliseconds, etc. 

Back to top 


ubundom Newbie
Joined: 22 Oct 2008 Posts: 2

Posted: Tue Nov 25, 2008 4:24 am Post subject: Decimal Places 


Thanks to ftack for the OOo Tools>Options>Calculate>Decimal Places set to 8 tip! I couldn't for the life of me work out why the edit bar was showing 1.45 and I knew that the cell contained 1.4519871270. Fiddling with Format>Cells>Numbers didn't help at all ... phew!
What is puzzling me is that if I have a number format of 0.000;0.000;" " then the edit bar will show the value rounded to 8 dp: 1.45198713. If I leave the format as 0.000;0.000 then the edit bar will show the full unrounded value: 1.4519871270. This seems to be inconsistent behaviours, maybe it is fixed in 3.0.0?
Meanwhile the cell show the correct 1.452.
Surely the value displayed in the edit bar shouldn't be affected by a format code!? _________________ kubuntu 8.04 and migrating OOo 2.4.1 to 3.0.0 

Back to top 


