The FIXED Function in excel is an essential type of Mathematical Function. It is one of the simplest and easy to understand excel formulas you would ever find.
So let us begin learning the usage of FIXED Function in Excel.
When to Use the FIXED Function in Excel?
The FIXED Function in Excel formats a numerical value by fixing the number of digits we want after the decimal place. The FIXED Function rounds off the digits after the decimal to a specific number of digits we want. We can also toggle the usage of commas in writing the numbers.
The FIXED function is a specific case for number formatting in excel with the only difference being that the result of the FIXED Function is always a number formatted as text.
Syntax and Arguments
=FIXED(number,[decimal],[no_comma])
The following points contain essential information about the FIXED function in excel.
- number – This is the numerical value we are willing to format. It can be a hardcoded number or reference to a cell in the worksheet.
- [decimals] – It is an optional argument and the default value is 2. Decimals is the number of digits to which number will be rounded on the right of the decimal point. If the Decimals is negative, then the number would be rounded off to the left of the decimal point.
- [no_commas] – This argument asks for a boolean input on whether we do not need commas in the resultant number.
- TRUE – The resultant number would not contain commas
- FALSE (default value) – Number would contain commas
Important Notes about FIXED Function
Here are some important points to know about the FIXED Function Formula as mentioned below.
- The FIXED Function is an alternative to round off the digits using number formats like 0.00 or ###0.0
- By default, the digits are rounded off to two decimal places. The default value for decimal is 2.
- For any text string supplied as the function input, we would get a #VALUE! Error as the function result.
- The function interpreted logical values TRUE and FALSE as 1 and 0 respectively.
- When the dates are supplied as number argument then it would be considered as Numbers.
- You can use the TEXT Function of Excel for more flexibility.
Examples to Implement FIXED Function
In this section of the blog, we are going to implement some examples for the FIXED Function.
Example 1 – Rounding Off the Constant
Let us suppose we have got the value of π by using the pie function as follows.
We only needed three digits after the decimal place in 3.141592654
We would now use the FIXED Function to round off the value of π.
=FIXED(PI(),3)
As a result, we value of π was rounded off up to three decimal places.
Explanation – The PI Function in excel returns a constant value of π. We have supplied the number argument as the PI Function because we wanted to round off the value of π. Thereafter, the decimal argument is 3 as we want to round off up to three digits after the decimal.
Example 2 – Other Examples for FIXED Function
We would now look at some other examples of FIXED Function as implemented below
Explanation
Formula 1 – In the first formula, we have rounded off the number 1234.214 up to one decimal place which results in 1234.2
Formula 2 – In this example, we wanted to round off the number 134005 up to three decimal digits. Since there were no digits after decimal in 134005, the function adds zeroes at the right of the decimal to make it 134005.000
Formula 3 – Here we have specified the number as 12502. We did not mention the second decimal argument. Therefore the function considers its default value which is 2. The third argument no_commas is TRUE to remove the commas from the result.
Formula 4 – The number is taken as 1234. There is a negative value for decimal which would round off the number to the left up to three digits making it 1000.
This brings us to the end of the FIXED Function article.
Thank you for reading.