Excel FIXED Function – How To Use

The FIXED function in Excel rounds off the input number as per the required decimal places and converts the number to text optionally including the decimal point and commas.

The placement of commas and periods used as separators depends on the local language setting. The FIXED function is categorized as a text function in Excel.

Syntax

The syntax of the FIXED function is as follows.

Arguments:

The FIXED function accepts three arguments, out of which one is mandatory.

'number' – This is a mandatory argument. The input value that needs to be converted and formatted is inserted as this argument. It can be a direct value or a cell reference.

'decimals' – The second argument of the FIXED function is optional. It indicates the required number of places to the right of the decimal point. The input number is rounded off as per the value in the decimals If left blank, the argument's default value is 2, and if a negative value is used, the function counts and rounds off the input number to the left of the decimal point.

'no_commas' – The third argument is also optional. It is a logical argument that accepts TRUE or FALSE as input values. If TRUE, commas are not included in the return value. If left blank, the default value is FALSE, which includes commas in the return value. Commas here refer to thousands separators.

Important Characteristics of the FIXED function

The most basic feature of the FIXED function is that it converts the input value to text format. The return value of the FIXED function can only be used as a text value and cannot be utilized in numerical computations. Other attributes of the said Excel function are as follows.

• If the value in the numbers argument is non-numeric, the FIXED function returns a #VALUE! or #NAME? error.
• If the input value of the numbers argument is more than 15 significant digits, after the 15th digit, Excel replaces the subsequent digits with 0.
• The maximum input value of the decimals argument can be 127; anything beyond that results in a #VALUE! error.
• The no_commas argument returns a #VALUE! or #NAME? error for a non-numeric argument and a comma-less output for a numeric argument other than 0.
• The placement of commas in the return value of the FIXED function is as per the local language computer setting.

Examples of the FIXED Function

As we have already learned that the FIXED function has one mandatory argument and two optional arguments, let's find out the return value when only the compulsory argument is used in the FIXED function.

This example will help us understand the default nature of the FIXED function. We have taken a few sample values for the first argument in column B. The formula used will be as follows.

When the optional arguments are not used, the default value of the decimals argument is 2, which means the FIXED function will round off the input value to two places on the right of the decimal point. The default value of the no_commas argument is FALSE, which means commas are displayed in the final return value.

As observed from the example dataset, all values in column B are rounded off to two decimal places

In the last example in cell B6, the FIXED function rounds off the input value to 2 and adds commas in the return value. We can also notice the left alignment of all the return values in column C compared to right-aligned values in column B, indicating that the FIXED function converted the numeric format to text format.

Let's explore further and learn how the FIXED function behaves with different input arguments.

Example 1 – Basic Vanilla FIXED Function

In this example, we have tried to include all possible combinations of the input arguments to understand the FIXED function. The dataset consists of input values of all three arguments in columns B, C, and D.

The formula used will be as follows.

The FIXED function uses the basic rounding-off rule to the number of digits mentioned in the decimals argument. Let's understand the return value of the FIXED function with different input arguments.

The first case in row 3 is when the decimals argument is 0, and the no_commas argument is left blank. The FIXED function transforms the input value to text and rounds it off to zero decimal places while including commas.

The second case in cell B4 is similar, but the no_commas argument is TRUE, which indicates no commas should be included in the return value. As the decimals argument cell is empty, the function rounds off to zero places hence the return value is 5870 with no commas.

The value in cell B5 is 125698.2356, and the no_commas argument is empty, which means FALSE. The return value will be rounded off to 125,698.2 with commas.

As in the case of cell B6, commas will not be included, and the number will be rounded to 1 decimal place.

The next example in cell B7 is pretty straightforward, where the function will round off the input value to 2 places and include commas as the third argument value is empty, which is FALSE.

The last example is if the first argument number is empty, the FIXED function assumes it to be 0 and returns the value as per other arguments. Here, the decimals value is 3 without commas, so the return value is 0.000.

We have finally understood how the FIXED function behaves with a positive decimals value. Now, let's find out how the function behaves with a negative decimals value.

Example 2 – Negative Value in Decimals Argument

We know that a negative value in the decimals arguments indicates the position to the left of the decimal point for rounding off. When we wish to round off the value to the left of the decimal point, it is usually done based on the closest ones, tens, hundreds, etc.

Let's understand the FIXED function with various negative decimals values. We have taken different values for all three arguments of the FIXED function.

The formula used will be as follows.

The first value in cell B3 is to be rounded off to tens places on the left as the value of the decimals argument is -1. The no_commas argument is empty, so commas would be included in the return value. As it is nearer to 5610, the FIXED function converts the value to text, rounds off to the closest tens place, and includes commas as seen in cell E3.

The next example in cells B5 and B6 have the same decimals argument value, which is -2, which means both must be rounded off to two places on the left of the decimal point. The first one in cell B5 is rounded off from 1526.2 to 1500 as the closest hundreds value is 1500, whereas the value in cell B6 is rounded off to 1600, which is the closest hundreds value to 1556.2. The commas are included and excluded respectively as per the third argument.

The last example is where the input value is 20, and it needs to be rounded off to -1 places which is the thousands places on the left. The result is zero (0). So, if the absolute value of the decimals argument is more than the number of digits before the decimal point in the numbers argument, the result is always zero.

We have finally covered all possible scenarios of input arguments for the FIXED function. Now, let's explore other applications of the function.

Example 3 – Combining Numbers with Text

When combining numbers with text, the numbers tend to lose their formatting in Excel. The Number format option in Excel includes a thousand separator option to make the number easy to read and understand. For instance, reading 60,000,000 is easier to comprehend as 60 million compared to 60000000.

Here, we have taken 60,000,000 in cell B2 in Number format. When combined with text using the & operator, the return value in cell C2 loses its formatting. The formula used is as follows.

The FIXED function is useful in such scenarios where we need the number to retain its formatting when it is combined with text.

Suppose we have downloaded data on the country's population in 2022 from the web. The dataset contains the country's name in column B and the population of 2022 in column C. We wish to combine them with text to use them in emails further.

The formula used is as follows.

As observed in column D, the formatting of the numbers is not included. For the sake of presentation and comprehensibility, we will use the FIXED function and then combine it with text.

The formula used will be as follows.

The input value, which is Population 2022 in this case, will first be formatted using the FIXED function and then combined with text using the & operator. We have used 0 as the input value of the decimals argument as we want the population count in integers. The no_commas argument is the default value which means commas will be included.

Now we can directly use the return text in emails, messages, or wherever required.

Example 4 – Calculating Within the FIXED Function

Another interesting characteristic of the FIXED function is that we can do basic calculations within the function. Suppose we have a dataset containing marks secured by students in four subjects. We now wish to send them a message about the total marks secured.

One way is to calculate the sum of the marks secured and then manually create messages to send them. Instead, we can make this a one-step process using the FIXED function. The formula used will be as follows.

We will pick up the student's name from column B and then join it with the & operator with the required text. We will then calculate the total within the FIXED function and join it with the remaining text. The FIXED function will convert the sum into text format making it easier to combine with text.

We can directly message these to the respective recipients.

FIXED Function vs. TEXT Function

We have already learned that the FIXED function converts the numeric value to text and then formats it as per the decimal places and commas. Compared to the TEXT function, the FIXED function only applies number formatting, whereas the TEXT function is more versatile.

The TEXT function converts any format to text format and is used for dates, currency, time, percentage, etc. The FIXED function uses the following format to transform the input value – 0.00 or #,##0, while the TEXT Function accepts the format_text as an argument. In a way, the FIXED function is a subset of the TEXT function.

FIXED Function vs. Number Format

The major difference between the FIXED function and the Number format is that the FIXED function converts the input value to text format. In contrast, the Number format just changes how data is displayed without changing the actual value of the data.

So, the Number format is preferred if we wish to use the data for further numeric calculation.

In this example, we have taken a sample number and displayed it using the FIXED function and the Number format.

The value in cell B3 is the default FIXED function, where the formula is as follows.

For the value in cell C3, simply select the Number option and choose the format group on the Home tab.

Both the FIXED function and Number format allow flexibility in including commas and choosing the number of decimal places to display.

In the FIXED function, we can enter the desired value as input for the optional decimals arguments. For the Number format, select the input cell and then click on the Number group in the Home tab. We can then choose 'More Number Formats'. A pop-up window will open, as shown below.

Here, we can choose how many decimal places to show and whether we wish to include commas. To include the commas, check the option 'Use 1000 Separator (,)'.

The negative numbers in the FIXED function are displayed simply with a negative sign in the front, whereas, in the Number format, we have the option to choose the display. So, if you wish to use the data for calculations, use the Number format but if you wish to join the numbers with text, use the FIXED function.

Hopefully, all the aspects and applications of the FIXED function are now understood. Practice and discover new ways to utilize the FIXED function while we curate new Excel function tutorials for you.