Formatting cell data in Calc part II: numbers
Posted by Greten on 13 Apr 2013 under Tools
This post assumes that you already selected the cell(s) that you want to format and already opened the Format Cells option window. If not, please visit this prerequisite post on cell formatting.
This is the formatting for real numbers. The initial default format is called "General". It has negative sign for negative numbers, and no sign for positive numbers and zero. It has whole number part on the left of decimal point and decimal number part on the right side. If you type a whole number, there will be no decimal point. If you type a decimal number with no whole part, it will be preceded by zero and decimal point. It will accept whatever number you type and does not add extra zeroes or round-off.
You will notice in the lower part of the Format Cell option window that its Format Code is "General". We just described in the previous paragraph how General format behaves. It's different from the others because it does not use special codes to represent possible digits and how they will behave.
Leading zeroes and decimal places
You can adjust the decimal places and leading zeroes. Once you did, you will notice that the word "General" is now replaced by 0, 00, 00.00 or any combination of zeroes and a decimal point. Under the field where General was situated, you will notice the word "User-defined" appearing. Since you made changes, it is no longer the General format, but a User-defined format as defined by the combinations of zeroes and decimal point. You can do the same if you choose the "User-defined" among the categories, except that you need to manually type the combination of zeroes. By starting with General, you are creating your own user-defined format modified from the General format.
The option "Leading zeroes" refers to the mininum number of digits that will appear in the whole number side. If for example, you set the leading zeroes to three. The number 3 will be displayed as 003, the number 33 will be displayed as 033, and the number 333 will be displayed as 333. You get the pattern? Now if you have a four-digit number, that's the only time Calc will put additional digits, so 3333 will be displayed as 3333.
You can adjust the number of decimal places by changing it from 0 to 1, 2 and so on. The cell will display the number of decimal places as indicated, with 0 replacing the a digit if not applicable. For example, you set the decimal places to 3, it will always have three decimal places no matter what. A whole number, say 1 will be displayed as 1.000 and 0.25 will be displayed as 0.250. Suppose you have a decimal number 0.0435, since it already exceeded the required number of decimal places, it will be rounded-off and displayed as 0.044.
In the screenshot above, the numbers above the Format cells option window are formatted in a manner prescribed in the format code. Notice 126.400 automatically added a digit since it exceeded the 2 leading zeroes, but 03.510 has extra zero on the left just to keep up with having two digits in the whole number part. On the decimal part, there are always three digits with zeroes replacing digits that are not applicable.
Confusing adjustment of decimal places
Adjusting the decimal places can be confusing for two reasons. First, if you started in "General" Format code, you will see that the decimal places is set to 0, and yet the cell is accepting any number of digits of decimal places. You can type 0.1, 0.12, 0.1212, and so on and Calc will display it as such. However, once you tinker with either decimal places or leading zeroes, the "General" changes into some codes with zeroes. The decimal part is set to the number of digits based on decimal places that you choose. If you made it 0 again, or if it's always been 0 since you tinker only the leading zeroes on the whole number side, the number will be rounded of to the nearest whole number e.g., 11.5 is displayed as 12.
Second, the rounding-off is applied only as displayed in the cell. For example, you have number 12.3456 and you set the decimal places to 1. The cell will display 12.3. If you moved the cell pointer and checked the input line, you will see that the value inside the cell is actually 12.3456. If this cell was used in computations, the number that it will use is 12.3456 and not 12.3. It is important for you to be aware of how you configured the decimal places of the cells if you will be constructing a spreadsheet problem generator as the given values being displayed might be different from the one that is actually being used in computations.
Note that you cannot edit what is shown in the input line. It is how Calc stores its data. The format options in cells are merely for presentation.
Hash and zeroes in the Format code
The only way to remedy the first cause of confusion in decimal places (mentioned two paragraphs above) to either type "General" again in the Format code, or encode those at the right of decimal point as .#######, instead of 0.0, or 0.00 if you set the decimal places to 1 or 2 respectively. If you have a thousand separator, the Format code can become #,##0.#######. Note that there are six hash signs on the decimal side (right of decimal point). The hash signs on the left and the right of decimal point behaves in a different manner.
The behavior of hash is different on either sides of the decimal point. Suppose you have a format code of #.###. On the left side of the decimal point, digits will always be added whenever needed. 1 is shown as 1, 11 as 11, 111 as 111 and so on. The encoding #.###, ##.### and even #####.### would not have any difference in output. They show a digit when needed and not when not needed. The only purpose of encoding it as #,##0.### is to mark that that thousand separator should appear every third digit. However, on the decimal side, the hash represents the maximum number of decimal places to be displayed. The difference between it and #0.000 is that it does not display trailing zeroes. Supporse we have Format code of #0.### in a cell. If we encoded 0.2, it will appear as 0.2. If 0.23 then 0.23. If 0.234, then 0.234. However, if we encode 0.2345, since the digits already exceeded the number of hash signs, it will be rounded-off as 0.235.
Thousands separator and decimal mark
On the right, there is a "thousands separator" checkbox. Click it if you want to display the number as 3,333. Note that once you checked the thousands separator checkbox, the "General" in the Format code will disappear and thus, you need to intervene if you want the decimal places to work in a manner similar to that of General format or in some other ways.
In many English speaking countries such as Australia, India, United Kingdom, the United States, the Philippines, and Zimbabwe, the typical number format is that comma(,) is used as thousands separator and period (.) is used as decimal mark; hence it's also called decimal point. I am not sure how this exactly works, but if you installed OpenOffice or LibreOffice in your computer, they seem to detect from where you are downloading and thus configure your settings based on it. With me being from the Philippines, I got the comma as thousands separator and the period as decimal mark.
However, there are other possible thousands separators and decimal marks. Some countries use the comma as decimal mark instead and in those countries, they don't use comma as thousand separator to avoid confusion. Other possible thousands separator include period (basically it just switch place with comma) and character space.
For some reasons, manipulating the Format code alone will not allow you to get the thousand separator - decimal mark that that you need. If for example, the format code generated if I checked the thousands separator and set the decimal places to two is this:
While I can tweak the hashes and zeroes, there's nothing I can do with the comma and period that would still result in the way that I want. Say, if I replace the comma with character space, e.g., # ##0.00 or # ###.00, the character space will appear only between the hundred and the thousand digits. I was experimenting on this because here in the Philippines, the character space is also an accepted thousands separator as seen in our local textbooks and thus I'm looking for a way to replicate it.
A solution that I do not really consider elegant but this is what works for now is to select another language for that cell by selecting from among the dropdown list in the upper right corner, still in the Numbers tab of Format cells option window.
In the above example, we tried formatting the numbers such that the period is the decimal mark and character space is the thousand separator. We are able to obtain it by selecting Georgian as language. Notice that the Format code is now # ##0.0, the same Format code that I tried earlier but resulted to having space only between hundreds and thousands digit and nowhere else. Calc apparently accepted it because the language is now in Georgia.
Take a look at the following table to know which language you should use to have certain combination of thousands separator and decimal marker. I did not test all languages; just enough to show the different possible combinations.
|Thousand separator||Decimal mark||Languages||Example|
|comma (,)||period (.)||English (all variations)||1,111,111.111|
|period (.)||comma (,)||French (Belgium), Dutch (both variations)||1.111.111,111|
|space ( )||comma (,)||French (all variations except Belgium and Switzerland)||1 111 111,111|
|space ( )||period (.)||Georgian||1 111 111.111|
I did not include Arabic languages here since they use their own symbols as thousands separator and decimal mark; symbols that seems to be exclusively Arabic. Their thousands separator looks like a single quote at first glance, but it's really not. Their decimal mark looks like an elongated comma that is clearly distinguishable from the actual comma.
Last updated on 25 Aug 2013.
Share your thoughts
* Required. Your email will never be displayed in public.