Documentation/Calc Functions/FIXED
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
FIXED
Category:
Text
Summary:
Converts a number to a text representation in a specified format.
Syntax:
FIXED(Number[; Decimals[; No Thousands Separators]])
Returns:
Returns a text string representing the given number in the specified format.
Arguments:
Number is a real number, or a reference to a cell containing a real number, that is to be converted to a text representation.
Decimals is an integer in the range -15 ≤ d ≤ 15, or a reference to a cell containing such an integer, that is the number of decimal places to be used in the text representation. If Decimals is omitted, a default value of 2 is applied. FIXED rounds Number to the specified number of decimal places. If Decimals is negative, Number is rounded to the left of the decimal separator (for example, a Decimals value of -1 rounds to the nearest 10, while a Decimals value of -2 rounds to the nearest 100).
No Thousands Separators is a logical value that controls whether digit group separators are included in the text representation. If set to 0, set to FALSE, or omitted, then digit group separators are included according to locale. If set to any other value, digit group separators are omitted.
- If any of Number, Decimals, or No Thousands Separators is non-numeric, then FIXED reports a #VALUE! error.
- If Decimals is a non-integer value, then FIXED converts it to an integer by taking its floor value.
- If, after taking its floor value, Decimals is less than -15 or greater than 15, then FIXED reports an invalid argument error (Err:502).
Additional details:
The symbol used to separate the integer part of a number from its fractional part is known as a "decimal separator". Various decimal separators are used around the world, dependent on locale. For example, a decimal point (full stop) is used in many countries, a decimal comma is used in many other countries, and an Arabic decimal separator (Unicode character U+066B) is used in the Arab world.
To make reading easier, the digits of longer numbers are often grouped using a delimiter that depends on locale. For example, comma, full stop, and space may be used as the "digit group separator". In many locales, the integer components of numbers are broken up using a delimiter every three digits and this is often known as a "thousands separator". However, in some cases the delimiter is not placed every three digits, and in some cases the fractional component of a number is written using digit group separators. Hence the term "thousands separator" is not universally applicable.
The FIXED function depends on locale settings and therefore should not be regarded as portable.
Examples:
The results of the following formulas are described for the English (USA) locale - results may be different for other locales.
| Formula | Description | Returns |
|---|---|---|
| =FIXED(1,234,567.89; 3; 1) | Here the function returns a string representing the specified number rounded to three decimal places, without digit group separators. | 1,234,567.890 |
| =FIXED(E1; E2; E3) where cells E1, E2, and E3 contain the values 1234567.89, 3, and TRUE respectively. | Here the function again returns a string representing the specified number rounded to three decimal places, without digit group separators. | 1,234,567.890 |
| =FIXED(1,234,567.89) | Here the function returns a string representing the specified number rounded to two decimal places (the default when the Decimals argument is omitted), with digit group separators (the default when the No Thousands Separators argument is omitted). | 1,234,567.89 |
| =FIXED(12,345.6789; −2) | Here the function returns a string representing the specified number rounded in front of the decimal separator to the nearest 100, with digit group separators (the default when the No Thousands Separators argument is omitted). | 12,300 |
| =FIXED(12,345.6789; −2.1) | Here the function returns a string representing the specified number rounded in front of the decimal separator to the nearest 1000 (-3 is the floor value of -2.1), with digit group separators (the default when the No Thousands Separators argument is omitted). | 12,000 |
| =FIXED(12,345.6789; 3.6; 1) | Here the function returns a string representing the specified number rounded to three decimal places (3 is the floor value of 3.6), without digit group separators. | 12345.679 |
| =FIXED(12,345.6789; ; 1) | Here the function returns a string representing the specified number rounded to two decimal places (the default when the Decimals argument is omitted), without digit group separators. | 12345.68 |
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
FIXED