Ask Your Question

Calc: Is it possible to display the same number of decimal places as formulae input cells

asked 2017-02-02 16:54:04 +0100

Cantagril gravatar image

For example, I'm subtracting a series of pairs of numbers in one column from another and displaying the result in a third. Some of these number pairs are five digits to one decimal place (e.g 17382.1) and others are less than 1 but to 5 decimal places (e.g 0.85467)

At the moment I have to set the cell format to 5 decimal places for the multi decimal place numbers to display correctly and this has the effect of displaying ALL the results to 5 places even when they are whole numbers - e.g 25324 is displayed as 25234.00000 and so on. This makes the column an unnecessary 10 digits wide with the redundant zeroes making the numbers messy and less easy to read.

Is there a way to: A) display only the number of decimal places as in the input cells? B) Limit the the total number of displayed digits to 6? C) Somehow reduce the column width to 6 digits wide without getting the hashes instead of a number?

As a beginner, any relatively polite suggestions will be most welcome.

Many thanks in advance

edit retag flag offensive close merge delete


Shouldn't the 'General' number format do it just the way you want? In what way does it not meet your aspirations?

By the way: I cannot admit your statement the.. "redundant zeroes making the numbers messy and less easy to read". To the contrary I would expect numbers in one column to be aligned with resptect to the decimal separator. Whether or not the trailing zeros are significant or may simulate an accuracy not actually achieved, is another question.

Lupp gravatar imageLupp ( 2017-02-02 18:45:56 +0100 )edit

Thanks very much for your input Monsieur and you are, of course, absolutely right about the General format. I omitted to mention that I want to display a decimal point followed by a zero for the whole numbers, which I wasn't getting. Senor Kaganski (below) has provided with me the perfect answer for me in using "hashes" to replace the zeroes where I'd want them displayed. I do appreciate your prompt answer though.

Cantagril gravatar imageCantagril ( 2017-02-06 17:28:14 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-02-02 21:49:37 +0100

When you define decimals number in Format Cells dialog (using up/down spin buttons), you create the Format code in the bottom line. The number of decimal digits is defined by "0" (zeroes) after decimal separator. Zero means "always show decimal value in this position".

You may, however, replace those zeroes by "#" - which means "show decimal value, if non-zero". So, a Format code of 00000.00000 will always output all 11 characters in result (5 digits before, decimal separator, 5 digits after); 0.##### will give you only required amount of digits after separator (but ensures 5-digit precision if available), and will always show at least 1 digit before separator; and # ###.##### will create thousand separator if required, and show only significant digits both before and after decimal separator.

Help button provides you with description of the dialog controls, and also a link to Number Format Codes topic.

edit flag offensive delete link more


Perfect! Thank you so much:-) I missed out one point that Mr Lupp above pointed out - in the circumstances I outlined, the "Regular" format would have sufficed. In fact, I had not added that I wanted the whole numbers displayed as 00000.0 and the following 4 digits not displayed unless showing a value. I have just altered the format to 0.0#### and this is just right for my purposes....and thanks for the link to the Number Format Codes Topic. All the best, C

Cantagril gravatar imageCantagril ( 2017-02-06 17:34:03 +0100 )edit

answered 2017-02-02 18:55:59 +0100

karolus gravatar image


A. with untouched Formatsettings Calc shows exactly that behaviour.

B. …

C. Maybe the option [x]adopt to cellsize in the Textlayout-tab does fit you needs (1)

(1)sorry I'm working with german-Gui, maybe no good translation

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-02-02 16:54:04 +0100

Seen: 788 times

Last updated: Feb 02 '17