FIXED function
Number
The number to format.
DecimalPlaces
The number of decimal places to use. May be negative, in which case the number is rounded to the left of the decimal point. If omitted, 2 is assumed.
OmitThousandsSeparators
FALSE to include thousands separators and TRUE to exclude them. If omitted, FALSE is assumed.
Returns
A formatted textual representation of a number.
Returns a number as text with a specified format. This function takes the app's configured language into account. FIXED(42.12)FIXED(42,12) returns "42.12" if the language of the app is set to US English and "42,12" if the language is set to French.
Decimal places
Use the second parameter to set the number of decimal places the returned number is formatted with. FIXED(1234.56789, 3)FIXED(1234,56789; 3) returns "1,234.568" (rounded to three decimal places) if the language is set to US English. FIXED(1234.56789, 3, FALSE)FIXED(1234,56789; 3; FALSE) returns "1234.568", without the thousands separator, because of the third parameter, FALSE.
Rounding to the left of the decimal separator
The second parameter can be set to a negative number, in which case the number is rounded to the left of the decimal separator. FIXED(1234.56789, 0)FIXED(1234,56789; 0) returns "1,235", FIXED(1234.56789, -1)FIXED(1234,56789; -1) returns "1,230", FIXED(1234.56789, -2)FIXED(1234,56789; -2) returns "1,200" and FIXED(1234.56789, -3)FIXED(1234,56789; -3) returns "1,000".
Related functions
- Use FORMATFRACTION to format fractions (0.75 can be formatted as "3/4").
- Use FORMATPERCENTAGE to format percentages (0.75 can be formatted as "75%").
- Use FORMATSCIENTIFIC to use scientific notation (1,500 can be formatted as "1.5E+03").
- Use PARSENUMBER to turn the text returned from this function back into a number.
- Use FORMATNUMBER to access features not offered by FIXED. FORMATNUMBER can pad the returned number with leading zeroes ("00123"), use accounting style for negative numbers (enclosed in parentheses) and offers more flexibility in terms of specifying the number of decimal places a number should be formatted with. However, only FIXED can round a number to the left of the decimal separator.
Examples
Returns "42.12" if the language of the app is set to US English and "42,12" if the language is set to French.
Returns "1,234.568" if the language is set to US English. The second parameter, 3, specifies that numbers should be formatted with three decimal places.
Returns "1234.568", without the thousands separator, because the third parameter is set to TRUE.
Returns "1,230". When the second parameter is set to a negative value, the number is rounded to the left of the decimal separator.
Returns "1,234.568" if the language is set to US English. The second and third parameters are both set to , 3, specifying that the number should be formatted with exactly three decimal places.