Decimal Places

Ed · Guest

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.

tburcham · General User Joined: 17 Apr 2003 Posts: 36

Hi Ed,
Try this as a user-defined format:
#.################
Is this what you were looking for?

tburcham

Ed · Guest

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.

ftack · 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. Wink

It is a rather stupid default to have general format numbers displayed with only two decimals.

Ed · Guest

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.

m3L · Newbie Joined: 08 Nov 2005 Posts: 1

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 ###

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

liv2cod · Newbie Joined: 09 Nov 2005 Posts: 2

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..10e-12. 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.

ubundom · Newbie Joined: 22 Oct 2008 Posts: 2

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