Excel Function: FIXED

Purpose

The FIXED function converts a numeric value, or a value that can be interpreted as one, into a text value. The FIXED function never expresses results in scientific notation and its output is always a value of type text. Values supplied to its arguments control the number of digits in the result’s mantissa and the presence of the digit grouping symbol. The operating system’s number formatting settings control:

The character used to represent the decimal point.
The character used as the digit grouping symbol.

Invocations of the FIXED function take the form:

FIXED(number,[decimals],[no_commas])

Limitations

The operating system’s number formatting settings have no influence over:
- The character used to represent the negative sign, which is always the standard minus symbol (-).
- The digit grouping convention, which is always “123,456,789.00000001”.

Arguments

Argument

Argument type

Description

number

Mandatory

A numeric value to be transformed into text.

decimals

Optional. Defaults to 2 if omitted.

An integer which:

determines how many digits will appear in the result’s mantissa.
controls the number of decimal places to which the result will be rounded.

no_commas

Optional. Defaults to FALSE if omitted.

A logical value which determines if the result will contain digit grouping symbols.

Value	Description
TRUE	Digit grouping symbols will be omitted from the result.
FALSE	Digit grouping symbols will be included in the result.

Related Functions

Function	Example	Purpose

Error Conditions

Error value	Condition type	Condition
#VALUE!	Bad argument	The value supplied to the number argument could not be interpreted as being a number.
#VALUE!	Runtime	The result generated by the FIXED function was greater than 255 characters in length.

Examples

Example	Formula	Result	Description
1	`=FIXED(1000000000.12345,4,TRUE)`	`1000000000.1235`	Convert a large-positive number with 5 digits in its mantissa to text. digit grouping symbols are omitted from the result because no_commas is TRUE. the mantissa is rounded to 4 decimal places. 0.12345 becomes 0.1235.
2	`=FIXED(1000000000.12345,4,FALSE)`	`1,000,000,000.1235`	Convert a large-positive number with 5 digits in its mantissa to text. digit grouping symbols are included in the result because no_commas is FALSE. the mantissa is rounded to 4 decimal places. 0.12345 becomes 0.1235.
3	`=FIXED(0.001293,7,TRUE)`	`0.0012930`	Convert a small-positive number with 6 digits in its mantissa to text. digit grouping symbols are omitted from the result because no_commas is TRUE. the mantissa is rounded to 7 decimal places 0.001293 becomes 0.0012930
4	`=FIXED(0.001293,7,FALSE)`	`0.0012930`	Convert a small-positive number with 6 digits in its mantissa to text. digit grouping symbols are omitted from the result because they can only appear to the left of the decimal point. 4 digits need to appear to the left of the decimal point for them to appear. the mantissa is rounded to 7 decimal places 0.001293 becomes 0.0012930
5	`=FIXED(9.421E+21,8,TRUE)`	`9421000000000000000000.00000000`	Convert a number with no mantissa expressed in scientific notation into text. the result is not expressed in scientific notation. digit grouping symbols are omitted from the result because no_commas is TRUE. the mantissa is padded with 8 zeroes.
6	`=FIXED(9.421E+21,8,FALSE)`	`9,421,000,000,000,000,000,000.00000000`	Convert a number with no mantissa expressed in scientific notation into text. the result is not expressed in scientific notation. digit grouping symbols are included in the result because no_commas is FALSE. the mantissa is padded with 8 zeroes.
7	`=FIXED(-1000000000.12345,4,TRUE)`	`-1000000000.1235`	Convert a large-negative number with 5 digits in its mantissa to text. digit grouping symbols are omitted from the result because no_commas is TRUE. the result is prefixed with a minus symbol (-) to indicate it is negative. the mantissa is rounded to 4 decimal places. 0.12345 becomes 0.1235.
8	`=FIXED(-1000000000.12345,4,FALSE)`	`-1,000,000,000.1235`	Convert a large-negative number with 5 digits in its mantissa to text. digit grouping symbols are included in the result because no_commas is FALSE. the result is prefixed with a minus symbol (-) to indicate it is negative. the mantissa is rounded to 4 decimal places. 0.12345 becomes 0.1235.