Formats and cultures

The actual way in which a number will be presented depends on the chosen format, but also on the chosen Regional Setting Culture. The culture can be set in the Configuration Format. In the example above the culture 'en-US' is used. If the culture is, for instance, 'nl-NL' then the format will remain the same, but the representation of the number will follow the dutch number notation. This means that the decimal mark will be a ',' and the thousands separator will be a '.'. From version 23.11 it is also possible to set the culture in a parameter of the formula. This way the culture can be set on a formula level instead of an application level, to ensure bilingual usage.

Difference between # and 0

The difference between using '#' and '0' is that on the position of # a number will appear only if it actually exists. Suppose we want to determine the format of the number 10. If we would use the format ##0.0 then the output will be 10.0. On the position of the first '#' is no text and will therefore not be shown. If we would use the format 000.0 then the output will be 010.0. On the position of the first '0' is no text but a 0 will be shown nonetheless.

Format of positive and negative numbers

So far we have assumed that the format of positive and negative numbers is to be the same. It is also possible to explicitly state the format of both sets of numbers. Suppose we would like to denote positive numbers with one decimal whereas negative numbers should be denoted with two decimals. This can be accomplished using the following notation: #,##0.0;-#,##0.00.

Furthermore, if we would like to add a separate notation for 0 then this can also be included in the notation: #,##0.0;-#,##0.00; zero. In this case 0 will be converted to the text 'zero'.