The FIXED function in Excel is a text function that rounds and converts numbers to text. It can be useful when you want to display numbers with a specific number of decimal places, or when you want to align numbers in different cells by using a fixed number of characters.
Syntax and arguments
The syntax of the FIXED function is:
=FIXED(number, decimals, no_commas)
The arguments are:
- number (required): The number that you want to round and convert to text.
- decimals (optional): The number of decimal places to round the number to. If omitted, it defaults to 2. If negative, it rounds the number to the left of the decimal point.
- no_commas (optional): A logical value that specifies whether to display commas in the returned text. If TRUE, it removes commas. If FALSE or omitted, it includes commas.
Example and explanation
Suppose you have a list of sales figures in column A, and you want to display them with two decimal places and commas in column B. You can use the FIXED function in column B with the following formula:
=FIXED(A2)
This formula will round the number in A2 to two decimal places and convert it to text with commas. For example, if A2 contains 123456.789, the formula will return “123,456.79” as text.
You can copy the formula down to the other cells in column B to get the same result for the other numbers in column A.
| Sales (A) | Formatted Sales (B) |
|---|---|
| 123456.789 | 123,456.79 |
| 98765.4321 | 98,765.43 |
| 54321.0987 | 54,321.10 |
| 8765.432 | 8,765.43 |
| 4321.09 | 4,321.09 |
If you want to display the numbers with a different number of decimal places, you can specify the decimals argument in the FIXED function. For example, if you want to display the numbers with no decimal places, you can use the formula:
=FIXED(A2,0)
This formula will round the number in A2 to the nearest integer and convert it to text with commas. For example, if A2 contains 123456.789, the formula will return “123,457” as text.
| Sales (A) | Formatted Sales (B) |
|---|---|
| 123456.789 | 123,457 |
| 98765.4321 | 98,765 |
| 54321.0987 | 54,321 |
| 8765.432 | 8,765 |
| 4321.09 | 4,321 |
If you want to display the numbers without commas, you can specify the no_commas argument as TRUE in the FIXED function. For example, if you want to display the numbers with two decimal places and no commas, you can use the formula:
=FIXED(A2,2,TRUE)
This formula will round the number in A2 to two decimal places and convert it to text without commas. For example, if A2 contains 123456.789, the formula will return “123456.79” as text.
| Sales (A) | Formatted Sales (B) |
|---|---|
| 123456.789 | 123456.79 |
| 98765.4321 | 98765.43 |
| 54321.0987 | 54321.10 |
| 8765.432 | 8765.43 |
| 4321.09 | 4321.09 |
Other approaches
The FIXED function is not the only way to round and convert numbers to text in Excel. You can also use the following functions:
- The TEXT function: This function allows you to format numbers as text using a custom format code. For example, you can use the formula
=TEXT(A2,"#,##0.00")to format the number in A2 with two decimal places and commas. You can use different format codes to achieve different results. For more information, see this article. - The ROUND function: This function allows you to round numbers to a specified number of decimal places. For example, you can use the formula
=ROUND(A2,2)to round the number in A2 to two decimal places. However, this function returns a number, not text. If you want to convert the result to text, you can use the & operator to concatenate it with an empty string. For example, you can use the formula=ROUND(A2,2)&""to round the number in A2 to two decimal places and convert it to text.
