FIXED Function in Excel: Explained
In this article, you will learn about the Fixed function and its uses in Excel.
What is a FIXED function in Excel?
In Excel, the FIXED formula is a built-in mathematical function that returns the rounded number of a given value to a specified number of decimal places, without changing the value itself. The function formats the number in decimal format using a period and commas and returns the result as text. The function is categorized under Excel TEXT functions.
Syntax of FIXED formula in Excel
The syntax of the Excel FIXED function is as follows:
where:
“number” - is the number to be converted to text.
“decimals”: - is the number of decimal places to be displayed. This parameter is optional. If omitted, it defaults to 2. If this argument is negative, the number is rounded to the left of the decimal point.
“no_commas”- is an optional argument that specifies whether to use commas as the thousands separator. If no_commas is TRUE, commas are not used. If no_commas is FALSE or omitted, commas are used.
Important notes about FIXED Function in Excel
- In Microsoft Excel, numbers can have a maximum of 15 significant digits, while decimals can have up to 127 places.
- If you do not specify the no_commas parameter in the FIXED function, or set it to FALSE, the resulting text will include commas as appropriate.
- If the decimals parameter is not included in the FIXED function, it defaults to 2.
- If the decimals parameter in the FIXED function is a negative value, the number argument is rounded to the left of the decimal point.
- The key difference between formatting a cell with a number using the Cells command and using the FIXED function directly is that the FIXED function always returns a text value, while formatting a cell with the Cells command maintains the number as a numeric value.
How to Use the FIXED function in Excel?
Understand the FIXED function with the help of an example case. The below dataset consists of a list of currencies and their corresponding conversion values against 1US$. Now the objective is to round the various currencies to one decimal place and make the numbers text.
Mapping of the dataset with the syntax of the FIXED function:
“number” - Cell C2 is the number that needs to be rounded off.
“decimals”: - As we need to round the number to one decimal place hence, we will put the value as “1”
“no_commas”- We will leave this argument blank, which is equivalent to inputting FALSE.
Hence our complete function would look as follows:
Now we need to apply this function in the corresponding cell D2 and press enter to generate the function result.
Now you can simply drag the function to cover all the cells where the function needs to be applied, and the amount needs to be rounded.
The function is instantly applied to all the cells, and the results are displayed in the column. The FIXED function rounds off the figures to one decimal place.
As you can observe, all the numbers are rounded to one decimal place instantly. Look at another similar case where you need to round the amounts with different criteria.
The dataset displays various conditions for rounding up the amount, let us understand each of the conditions and the usage of function for each case.
1. The first condition states to round the amount to 3 decimal places after the decimal point. Hence, we will use the FIXED function in Cell C2 and write the function as below:
Where A2 refers to the cell containing the amount, and 3 represents the third decimal place to the right of the decimal point. The result is 9,000.260.
2. The second condition states to round the amount to 1 decimal place before the decimal point. Hence, we will use the FIXED function in Cell C3 and write the formula as follows:
Where A3 refers to the cell containing the amount, and -1 represents one digit to the left of the decimal point. The result is 10.
3. The third condition states to round the amount to 2 decimal places after the decimal point without commas appearing. Hence, we will use the FIXED function in Cell C4 and write the function as follows:
Where A4 refers to the cell containing the amount, and 2 represents round the number to the second decimal place to the right of the decimal point. The TRUE argument prevents the formula from including commas in the returned text. The result is 825000.24.
4. The fourth condition states to round up the amount to 2 decimal places before the decimal point. Hence, we will use the FIXED function in Cell C5 and write the following function.
Where A5 refers to the cell containing the amount, and 2 means to round the figure to the second decimal place to the left of the decimal point. The outcome from the formula is 100.
5. The fifth condition states to round using the FIXED function without specifying any optional arguments. Hence, we will use the FIXED function in Cell C5 and write the below formula.
Where A6 refers to the cell containing the amount and, since no decimal point has been defined, by default, the formula considers it to be rounded to the second decimal place to the right of the decimal point. The function returns 36,74,000.64.
When should you use the FIXED Function in Excel?
You should use the FIXED function in Excel when you want to round a given number to a specific number of decimal places, without changing the underlying value of the number, and make it a text string. Here are examples of situations where you might want to use the FIXED function in Excel:
- When presenting financial data: If you are creating a financial report or presentation, especially when you want to generate narratives regarding financial data, you may want to round numbers to a certain number of decimal places for readability and consistency.
- When working with currency: If you are working with currency values, you may want to use the FIXED function to round the values to the nearest penny, cent, or other unit.
What is the difference between rounding functions and the FIXED formula in Excel?
The FIXED function always returns a text value. Unlike other rounding functions in Excel (such as ROUND, ROUNDUP, and ROUNDDOWN), the FIXED formula always returns a text value, even if the original value is a number. This means that the result of the function cannot be used in further mathematical calculations unless it is converted back to a number using the VALUE function or other functions. Bear this point in mind when you use the FIXED function in Excel.
