30 Excel Functions in 30 Days: 06 – FIXED

Icon30DayYesterday, in the 30XL30D challenge, we picked an item from a list with the CHOOSE function, and learned that other functions might be a better MATCH when doing a LOOKUP.

For day 6 in the challenge, we’ll examine the FIXED function, which formats a number with decimals and commas, and returns the result as text. That’s not too surprising, I guess, because the FIXED function is from the Text category. But is the FIXED function worth using? You can judge for yourself in the examples shown below.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the FIXED information and example, and if you have other tips or examples, please share them in the comments. Maybe I’m overlooking some awesome power that this function has, and this blog post needs to be FIXED!

Function 06: FIXED

The FIXED function rounds a number to a specified number of decimals, with or without comma separators, and returns the formatted result as text.

Fixed00

How Could You Use FIXED?

The FIXED function can change numbers to text, formatted with a set number of decimals. This could help in limited situations, such as:

  • Matching numbers imported as text from another application
  • formatting numbers in a text string

FIXED Syntax

The FIXED function has the following syntax:

  • FIXED(number,decimals,no_commas)
    • Number is the number you want to round and convert to text.
    • Decimals is the number of digits to the right of the decimal point.
    • If omitted, decimals defaults to 2
    • If negative, decimals round to the left of decimal point
    • If no_commas is FALSE or omitted, the result includes commas as usual
    • If no_commas is TRUE, commas are not included in the result.

FIXED Traps

The FIXED function changes a number to text, so don’t use it anywhere that you want to keep numbers as numbers. For example, if you want to limit the number of decimal places for a chart’s data, use number formatting in the cells, or use the ROUND function instead.

Example 1: Format Rounded Number

The FIXED function lets you take a number, round it to a specific number of decimals, add commas, and return the result as text. For example, with the number 32187.5689231 in cell C2, you could use the FIXED function to round it to 2 decimal places, and include a comma.

=FIXED(C2,2,FALSE)

Fixed01a

Instead of using FIXED, you could simply format the cell as Number format, with 2 decimal places, and a comma. That would leave the cell value unchanged — only its appearance would be different.

Fixed01b

Or, you could use the ROUND function, combined with cell formatting, to return a number that is rounded to two decimal places, and has a comma separator.

=ROUND(C2,2)

Fixed01c

Finally, if you really want the result as text, use the TEXT function. Its formatting options are much more flexible, and if you need rounding to the left, you can combine it with the ROUND function.

=TEXT(C2,”#,###.00?)

Fixed01d

Example 2: Round Number to the Left

With the FIXED function, you can also round to the left of the decimal point, by using a negative number for the decimal places argument. To remove commas, use TRUE in the no_commas argument. Again, the result is text.

=FIXED(C2,-2,TRUE)

Fixed02a

Or, you could use the ROUND function,  to return a number that is rounded to the left.

=ROUND(C2,-2)

Fixed02b

Example 3: Show Number as Thousands of Dollars

The FIXED function has limited formatting options, and only lets you control the number of decimal places, and the commas. You could use FIXED to round a number to the thousands, but can’t add a dollar sign, or hide the zeros.

=FIXED(C2,-3)

Fixed03a

Instead of using FIXED, you could format the cell with a Custom Number format of $#, that would leave the cell value unchanged — only its appearance would be different.

Fixed03b

Or, for a text result, use the TEXT function, which lets you format the number as thousands of dollars.

=TEXT(C3,”$#,”)

Fixed03c

Download the FIXED Function File

To see the formulas used in today’s examples, you can download the FIXED function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the FIXED Video

To see a demonstration of the examples in the FIXED function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Change Numbers to Text With Excel FIXED Function

 

_____________

You may also like...

5 Responses

  1. Ken Puls says:

    Hi Deb,

    Got to admit that I don’t see much use for this either. I read the first paragraph and was thinking “Doesn’t the TEXT function do that?” Curious if anyone does see some magic use for this one.

    Great series, too! :)

  2. Thanks Ken! As I explore some of these obscure functions, it becomes obvious why they aren’t used too often. ;-)

  3. Athena says:

    Under the “Fixed Syntax”; did you really mean say “If no_commas is True, commas are included…”? In example #2, commas are omitted. Which is true?

    PS: I am enjoying this series; I have often wondered if these functions would be helpful. Now I know.

  4. Thanks Athena, the Syntax section was incorrect, so I FIXED it. ;-)

    Some of these functions are useful, and it’s great to learn more about them, but others…not so much!

  5. Kirill Lapin (KL) says:

    Hi Debra,
    Great series! I can’t wait to read the post about CONCATENATE though, LOL

Leave a Reply

Your email address will not be published. Required fields are marked *