FIXED Function

The FIXED function converts a number to text, rounding to a given number of decimals. Like the Number format available on the home tab of the ribbon, the FIXED function will round the number as needed using the given number of decimal places. The main difference between applying a number format and using FIXED is that the FIXED function converts the number to text, whereas a number format just changes the way a number is displayed.

The FIXED function takes three arguments, number, decimals, and no_commas. Number is the number to convert. Decimals is the number of digits to which number will be rounded on the right of the decimal point. If decimals is negative, number will be rounded to the left of the decimal point. Decimals is optional and defaults to 2.

The no_commas argument is a Boolean that controls whether commas will be added to the result. No_commas is optional and defaults to FALSE. To prevent commas, set no_commas to TRUE.

Note: the FIXED function returns text and not a number, so the result cannot be used in a numeric calculation. If the goal is to format a number and retain its numeric property, applying a standard number format is a better option. Video: How to use number formatting.

Examples

In the example shown above, the formula in E5, copied down, is:

=FIXED(B5,C5,D5)

At each new row, FIXED returns a result based on the number in column B, the decimals in column C, and comma setting in column D.

Number is the only required argument. By default, FIXED will round to 2 decimal places and insert commas for thousands:

=FIXED(1000) // returns "1,000.00"
=FIXED(1000,0) // returns "1,000"
=FIXED(1000,0,FALSE) // returns "1000"

The FIXED function can be useful when you want to concatenate a number with other text. The example below shows how the output from the PI function can be trimmed by FIXED:

="PI is about "&PI() // returns "PI is about 3.14159265358979"
="PI is about "&FIXED(PI()) // returns "PI is about 3.14"

FIXED vs. TEXT

The FIXED function is a specialized function to apply Number formatting only. The TEXT function is a generalized function that does the same thing in a more flexible way. TEXT can convert numeric values to many different number formats, including currency, date, time, percentage, and so on.

Notes

• The output from FIXED is text. To simply format a number, apply a number format instead.
• The FIXED function converts a number to text using a number format like: 0.00 or #,##0
• The default for decimals is 2. If decimals is negative, number will be rounded to the left of the decimal point.
• The TEXT function is a more flexible way to achieve the same result.