FIXED function

The FIXED function in Excel is used to format a number as text with a specified number of decimal places and optional formatting for commas and currency symbols. The result is displayed as a number with the specified decimal places, but it is returned as text.

Syntax

=FIXED(number, num_digits, [no_commas])

Parameters:

• number (required): The number you want to format. This can be a numeric value, a cell reference, or an expression that results in a number.
• num_digits (required): The number of decimal places you want to display. If you specify 0, the result will be rounded to the nearest integer.
• no_commas (optional): A logical value (TRUE or FALSE) indicating whether to include commas in the number for thousands separators. If omitted, the default is FALSE, meaning commas will be included.

Key Points:

• Decimal Places: You can control the number of decimal places that the FIXED function displays.
• Text Output: The result of the FIXED function is text, not a number. This means it is used for display purposes but will need conversion back to a number for mathematical operations if required.
• Commas: By default, FIXED will add commas for thousands separators. You can suppress this behavior by setting the no_commas argument to TRUE.
• Currency Formatting: The function does not apply a currency symbol by default, but you can achieve this by using the TEXT function for more complex formatting, such as adding the dollar sign ($).

Example Usage

1. Basic Formatting with Decimal Places

If you want to format the number 12345.6789 with 2 decimal places:

=FIXED(12345.6789, 2)

This will return:

12,345.68

Note that the result is text and rounded to two decimal places.

2. Formatting Without Commas

If you want to format the same number but without commas:

=FIXED(12345.6789, 2, TRUE)

This will return:

12345.68

The commas are removed because the no_commas argument is set to TRUE.

3. Zero Decimal Places

If you want to round the number to no decimal places and just display the integer value:

=FIXED(12345.6789, 0)

This will return:

12,346

The function rounds the value to the nearest integer and includes commas by default.

4. Large Numbers

For a large number, such as 9876543.21, with 3 decimal places:

=FIXED(9876543.21, 3)

This will return:

9,876,543.210

The commas are added by default, and the number is displayed with 3 decimal places.

5. Using a Cell Reference

If cell A1 contains the value 1234567.89 and you want to format it with 2 decimal places:

=FIXED(A1, 2)

This will return:

1,234,567.89

Notes:

• Text Output: Since the FIXED function returns a text value, you cannot use the result in mathematical operations directly. If you need to convert it back to a number, you can use the VALUE function.
• Rounding: The FIXED function rounds numbers to the specified number of decimal places, so if you need to control rounding behavior more precisely, consider using the ROUND, ROUNDUP, or ROUNDDOWN functions.
• Locale Sensitivity: The use of commas and periods as thousands separators or decimal points depends on your regional settings in Excel. For instance, some locales use periods (.) as thousand separators and commas (,) as decimal points.

Related Functions:

• TEXT: If you need more flexibility in formatting (including adding currency symbols or custom number formats), you can use the TEXT function:

  =TEXT(12345.6789, "$#,##0.00")
  

• ROUND, ROUNDUP, ROUNDDOWN: These functions can round a number to a specific number of decimal places without converting the result to text.
• VALUE: If you need to convert the result of FIXED (which is text) back to a number, you can use the VALUE function:

  =VALUE(FIXED(A1, 2))
  

The FIXED function is useful when you need to display numbers in a specific format with a set number of decimal places, especially when preparing reports or for visual consistency, but remember that it returns a text value, which limits its use in further calculations.