The FIXED formula with example in Excel

The FIXED function in Excel is used to round a number to a specified number of decimal places and then convert the number to text with optional commas for thousands. This can be useful for formatting numbers for display purposes.

Syntax

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

• number: The number you want to round and convert to text.
• decimals: Optional. The number of digits to the right of the decimal point. If omitted, it defaults to 2.
• no_commas: Optional. A logical value that, if TRUE, prevents the function from including commas in the returned text. If omitted or FALSE, commas are included.

Examples

Let's look at some examples to see how the FIXED function works.

Example 1: Basic Usage

Suppose you have the number 1234.5678 in cell A1.

1. In cell A1, enter the number:

   excel
   1234.5678
   

2. In cell B1, enter the formula to round to 2 decimal places:

   excel
   =FIXED(A1, 2)
   

   This will output 1,234.57, rounding the number to two decimal places and including a comma for the thousand.

Example 2: Specifying Decimal Places

Suppose you want to round the number 1234.5678 to 1 decimal place.

1. In cell A2, enter the number:

   excel
   1234.5678
   

2. In cell B2, enter the formula to round to 1 decimal place:

   excel
   =FIXED(A2, 1)
   

   This will output 1,234.6, rounding the number to one decimal place and including a comma.

Example 3: No Commas

Suppose you want to format the number 1234.5678 without commas and with 2 decimal places.

1. In cell A3, enter the number:

   excel
   1234.5678
   

2. In cell B3, enter the formula to round to 2 decimal places without commas:

   excel
   =FIXED(A3, 2, TRUE)
   

   This will output 1234.57, rounding the number to two decimal places without including a comma.

Example 4: Default Decimal Places

Suppose you want to format the number 1234.5678 using the default number of decimal places (2).

1. In cell A4, enter the number:

   excel
   1234.5678
   

2. In cell B4, enter the formula without specifying the decimal places:

   excel
   =FIXED(A4)
   

   This will output 1,234.57, using the default of 2 decimal places and including a comma.

Practical Use Cases

1. Financial Reports: Displaying numbers in a standardized format with commas for thousands and a fixed number of decimal places.
2. Invoices: Formatting amounts in a readable way for billing and documentation.
3. Data Presentation: Ensuring consistent number formatting for presentations and printed reports.

Summary

The FIXED function in Excel is a useful tool for formatting numbers as text with a specified number of decimal places and optional commas for thousands. This function is particularly valuable for preparing financial reports, invoices, and other documents where consistent and readable number formatting is important. By understanding how to use the FIXED function, you can enhance the presentation and readability of your numerical data in Excel.