The Excel FIXED Function

Related Functions:

Function Description

The Excel Fixed function rounds a supplied number to a specified number of decimal places and then converts this into text.

The syntax of the function is:

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

Where the arguments are as follows:

number

The number to be converted into a text string.

[decimals]

An optional integer argument that specifies the number of decimal places to be displayed after the decimal point.

Note:

If [decimals] is omitted, it takes on the default value of 2;
If [decimals] is negative, the supplied number is rounded up to the left of the decimal point.

[no_commas]

An optional logical argument that specifies if the returned text should separate number groups (i.e. thousands, millions, etc.) by commas.

Possible values are:

TRUE	-	Commas are not included in the resulting text;
FALSE	-	Commas are included in the resulting text.

If the [no_commas] argument is omitted, it uses the default value of FALSE.

Fixed Function Examples

The following spreadsheet shows the Excel Fixed Function, used to convert the number 5123.591 into text, rounded to different numbers of decimal places.

Formulas:

	A
1	=FIXED( 5123.591 )
2	=FIXED( 5123.591, 1 )
3	=FIXED( 5123.591, 0 )
4	=FIXED( 5123.591, -1 )
5	=FIXED( 5123.591, -2 )
6	=FIXED( 5123.591, -3, TRUE )

Results:

	A
1	5,123.59
2	5,123.6
3	5,124
4	5,120
5	5,100
6	5000

Note that, in cell A1 of the above spreadsheet, the [decimals] argument is omitted from the Fixed function, so the default value of 2 is used.

See the Microsoft Office website for further details of the Excel Fixed function.

Fixed Function Error

If you get an error from the Excel Fixed Function, this is likely to be the #VALUE! error:

Common Error

#VALUE!

Occurs any of the supplied arguments are non-numeric.

Fixed Function Common Problem

Some users encounter the following problem when using the Excel Fixed function:

Common Problem:

Once a number has been converted, using the Excel Fixed function, it is stored in Excel as text. Therefore, it cannot be used in numeric calculations.

Solution:

If you want to be able to use your currency values in calculations, you should not use the Excel Fixed function. Instead, you should simply change the formatting of the original number into a currency format.

The easiest way to do this is to select the Currency formatting button on the Excel ribbon. This is generally located in the 'Number' group, on the Home tab of the ribbon (see below):

For further details of Excel Formatting, see the Excel formatting page.

Return to the Excel Text Functions Page
Return to the List of All Built-In Excel Functions