|
|
Number Formats in NeoOffice Calc
From NeoWiki
NeoOffice Calc provides several number formats. However, you can define your own formats to display numbers in a spreadsheet. In order to do that, you have to know certain rules.
The two main characters which are used in number format code are the hash sign # and the zero 0 which displays extra zeros if the number contains fewer digits than expected in the format.
A number format code can contain from one to three parts separated by a semicolon. If no condition is assigned
- A format code with only one part applies to all numbers.
- The first part of a code in two parts applies to positive numbers and to zero, the second part applies to negative numbers.
- If the code consists of three parts, the first one applies to positive numbers, the second to negative numbers and the third to zero.
Note: User-defined formats are bound to the document in which you define them. They are not available in another document. If you need to use them often, you have to define them in a template and to create your new documents from this template. See Editing Default Styles and Using Templates
Applying a format
- Select the cell you want to format.
- Go to the Format menu, and choose Cells…, or right-click or Control-click in the cell and choose Format Cells… in the contextual menu.
- In the Format Cells window which opens, click on the Number tab and select the category you wish (Number, Fraction, Scientific, etc.) in the Category secÂtion.
- Choose the format you want in the Format section. A preview field allows you to see the result that will be displayed in the cell.
- You can modify the number of decimal places and the number of non significant zeros by entering the values you want into the appropriate fields.
- You can also modify the number of decimal places displayed in a cell without invoking the Format Cells window by clicking on the Add Decimal Place (showing a .0 with a little blue mark) or on Delete Decimal Place (showing a .0 with a little red mark) button on the Formatting bar .
- You can add a thousands separator by checking the Thousands separator box. This separator depends on the chosen language.
Defining a custom format
If you want to apply a format which is missing in the list, you can define it in the field Format code.
- Select the cell you want to format.
- Go to the Format menu and click on Cells…, or right-click or Control-click in the cell and choose Format Cells… in the contextual menu.
- In the Format Cells window which opens, click on the Number tab and select User-defined in the Category list.
- In the Format code field, type the code you wish (see the examples in the tables below).
- Click on the Add button (green with a white mark), then click OK.
- This format is automatically added by NeoOffice to the appropriate category (Number, Fraction, Scientific, etc.), and when you select that format, User-defined is displayed under the Format code field.
Note: you can delete a user defined format by selecting it and clicking on the red icon with a white cross, to the right of the field Format code, then on OK.
Characters used in code
The following characters: #, 0, /, ?, @, as well as space, comma and period are used in code.
Non significant zeros
A zero is displayed in place of a 0 in code, if no digit is present in this location in the number.
Nothing is displayed in place of a # in code, if no digit is present in this location in the number.
Decimal numbers
You can choose the number of decimal places which will be displayed by giving it in the Decimals places box in the Options section. If you enter # signs after the period in the code, an integer will be displayed without decimal places. If you enter zeros after the period, an integer will be displayed with zeros in the decimal part.
The decimal separator depends on your language: period in English, comma in French.
Thousands separator
You can use the thousands separator you wish, depending on your language: comma, period or space. Check the Thousands separator box and enter the appropriate separator in the Format code field.
| Format code | Input | Output |
|---|---|---|
| # ###.## | 12345 | 12 345 |
| 12345.6 | 12 345.6 | |
| 12345.67 | 12 345.67 | |
| 12345.678 | 12 345.68 | |
| 0.12 | .12 | |
| # ##0.## | 0.12 | 0.12 |
| # ###.00 | 123 | 123.00 |
| # ### | 0 | empty cell |
| Standard format | 0 | 0 |
Fractions
You can display the decimal part of a number as a fraction by using a slash / and question marks ?.
| Format code | Input | Output |
|---|---|---|
| # ?/? | 1.2345 | 1 2/9 |
| # ??/?? | 1.2345 | 1 19/81 |
| # ??/??? | 1.2345 | 1 87/371 |
| 12.3456 | 12 216/625 |
Numbers as text
If you want that values appear as text, you can enter the @ character in the Format code field. You can also place an apostrophe in front of the number in the formula bar. This method allows to write numbers containing spaces (different from the thousands separator) or starting with a zero, such as phone numbers, for example: '123 456 789
The text is left aligned by default. You can change the alignment by clicking on the icon in the menu bar (showing a right aligned text).
Including text in number format code
If you want to include text, or characters such as parentheses, square brackets, hyphens or spaces in a number format, you need to place them between quotation marks " ". If the text contains a single character, you can place a backslash \ before it.
| Format code | Input | Output |
|---|---|---|
| "("000") "000"-"0000 | 0123456789 | (012) 345-6789 |
| \(000") "000"-"0000 | 0123456789 | (012) 345-6789 |
| #,##0.##" km" | 1234.56 | 1,234.56 km |
Conditions, Colors
You can define a format so that it only applies when certain conditions (three at the most) are met. For example, apply different colors to different values. Conditions must be placed between square brackets []. They are separated by a semicolon, and use the <, ≤, >, ≥, =, <> operators.
Black is the default color. The following colors: WHITE, BLUE, CYAN, GREEN, MAGENTA, RED, GREY are available. The condition, if any, must be placed in front of the color name.
| Format code | Input | Output |
|---|---|---|
|
[<=0][RED]# ##0.00;[>30][GREEN]# ##0.00;[BLUE]# ##0.00 | -10 | -10.00 |
| 15.25 | 15.25 | |
| 35.8 | 35.80 |
Rounding
If you enter a decimal number with more digits after the period than indicated in the format code, this number is rounded following the mathematical rule.
Percentage
To display numbers as percentage, you just have to add the % symbol to the number format, for example 0.00%
Scientific format
Scientific notation allows you to write very large numbers using the powers of 10. To define a scientific format, enter # or 0 followed by E+, e+, E- or e-.
| Format code | Input | Output |
|---|---|---|
| Standard | 1e3, 1E3, 1e+3, 1E+3 | 1.00E+003 |
| e-3 or 1E-3 | 1.00E-003 | |
| 0E+0 or #E+0 | 1000 | 1E+3 |
| 0.001 | 1E-3 |
Alignment on decimal separator
You can align decimal numbers correctly by using question marks to represent the digits after the decimal separator.
Note : if you want a good display, you have to use a non-proportional (or with fixed width) font , such as Andale Mono, Courier, Courier New or Monaco which are shipped with Mac OS X. Open Font Book in the /Applications folder to see what non-proportionnal fonts are available on your Mac
The table below has been created using Monaco font.
Currency format
The default currency format is defined in the Languages in the submenu Preferences… of the NeoOffice menu. You can select another currency in the drop-down menu under the Format section in the Format Cells.
