A.N.M. Mohaimen Shanto
The FIXED function is a built-in function in Microsoft Excel. It is categorized as a String/Text Function. It is one commonly used procedure in Excel. In this tutorial, you will learn every detail of the FIXED function in Excel. This tutorial will be on point with suitable examples and proper illustrations.
The above screenshot is an overview of the article. It represents a simplified application of the FIXED function in Excel. You’ll learn more about the dataset as well as the methods and procedures under different criteria in the following sections of this article.
Download Practice Workbook
Download this practice workbook.
Introduction to FIXED Function
⏺ Function Objective
The Microsoft Excel FIXED function returns a text illustration of a number. It rounds a number to the fixed number of decimals, formats the number in decimal format using a period and commas.
⏺ Syntax
⏺ Arguments Explanations
| Argument | Requirement | Description |
|---|---|---|
| number | Required | The number you want to round and convert to text. |
| [decimals] | Optional | The number of digits to the right of the decimal point.
-If [decimals] is skipped, it takes on the default value of 2; -If [decimals] is negative, the number will be rounded up to the left of the decimal end. |
| [no_commas] | Optional | This is a logical operator that is either TRUE or FALSE.
-If [no_commas] is TRUE, it will show value without a comma. -If [no_commas] is FALSE, there will be commas in the output. |
⏺ Returns
The FIXED function returns a value in Text/String format.
⏺ Available in
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.
How to Use FIXED Function
The FIXED function gives you value in a text form. Basically, our input is a number in a decimal form. Maybe you need a particular number of values after the decimal point. You can use this to show a specific number of values with or without commas.
Take a look at the following screenshot:
As you can see, we have some numbers in decimal form. Then, we have used the FIXED function on them. As you can see, the different results came out as we gave different values in the FIXED arguments. You have to just type the formula and insert your cell references or values in it.
6 Suitable Examples of FIXED Function in Excel
In the following section, we will provide you with six simple but effective examples. I hope it will give you a clear idea about the FIXED function in excel. We recommend you learn and apply all these methods to your dataset. It will surely enrich your Excel knowledge.
1. FIXED Function in Excel When ‘[decimal]’ Argument is Positive or Negative
Now, from the introduction, you have already learned that you have to provide a particular value in the position of the decimals. It will give you that number of numbers after the decimal portion.
So, if you give 2 in the decimal position, it will give you a rounded value with 2 decimal points.
=FIXED(123.4576,1) // result: 123.5
=FIXED(123.4576,2) // result: 123.46
=FIXED(123.4576,3) // result: 123.48
Now, what will happen if you give a negative number in the decimals argument?
Basically, if your given decimals are negative, it will round up to the left of the decimal point. But this time, there will be no decimal point.
=FIXED(5432.446,-1)// result: 5,430
=FIXED(5432.446,-2)// result: 5,400
=FIXED(5432.446,-3)// result: 5,000
Here, you can follow these simple steps.
📌 Steps
- Type the following formula in Cell D5:
=FIXED(B5,C5)
- After that, press Enter and drag the Fill handle icon.
Now, you can perform the same procedure with negative values like the following screenshot:
As you can see, we have successfully used the FIXED function of Excel.
Read More: How to Combine Text and Numbers in Excel and Keep Formatting
2. If ‘[decimals]’ Argument is Omitted in Excel
If your decimal points are blank, Excel automatically rounds up the number with 2 decimal points.
📌 Steps
- First, type the following formula in Cell C5:
=FIXED(B5,,TRUE)
Here, we are giving the no_commas parameter TRUE to remove the commas from the result.
- Then, press Enter and drag the Fill handle icon.
3. FIXED Function in Excel If the ‘[no_commas]’ Argument is TRUE or FALSE
Previously, we discussed the arguments of the FIXED function in Excel. In the no_commas, we can give it TRUE or FALSE.
If your no_commas argument is TRUE, there will be no commas in the result.
The opposite will happen if you set it to FALSE.
Follow these steps to have a better understanding:
📌 Steps
- First, type the following in Cell D5:
=FIXED(B5,C5,TRUE)
- Then, press Enter. After that, drag the Fill handle icon.
Similarly, if you want commas in the output you can set it to FALSE.
As you can see, we are successful in using the FIXED function both with commas and no commas.
Read More: How to Use Format Function in Excel (with Suitable Examples)
4. If ‘[no_commas]’ Argument is Omitted in Excel
If you omit the no_commas argument from the FIXED function, Excel will take FALSE as a default value.
📌 Steps
- First, type the following in Cell D5:
=FIXED(B5,C5)
- Then, press Enter. After that, drag the Fill handle icon.
5. FIXED Function with both ‘[decimals]’ and ‘[no_commas]’ Arguments are Omitted
Now, you may ask what would happen if I omit both decimals and no_commas argument?
In this case, Excel will do the following:
-if decimals is omitted, it will automatically set the decimal point to 2.
-if no_commas is omitted, it will automatically set the no_comnas to 0 or FALSE.
Follow these steps to have a clear idea.
📌 Steps
- First, type the following in Cell C5:
=FIXED(B5)
- Then, press Enter. After that, drag the Fill handle icon.
As you can see, we have successfully used the FIXED function in Excel with both omitted arguments.
6. Concatenate with FIXED Function Together with CONCAT in Excel
You can mix the CONCAT function with the FIXED function. You can mix it with any function to get your desired output.
The following screenshot will give you a clear idea:
Here, we used the FIXED function with the CONCATENATE function to get the decimal values with 2 decimal points.
Read More: How to Use TEXT Function to Format Codes in Excel
💬 Things to Remember
✎ The FIXED function gives us the output in Text/String form. You can change it using the Number Format.
✎ If you give non-numeric arguments, it will show #VALUE! Error.
✎ As the output is in text form, you can not use it for numeric calculations.
✎ Numbers in Microsoft Excel can have up to 15 significant digits, but decimals can be as large as 127.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the FIXED function in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website ExcelDemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
