A to Z of Excel Functions: The FIXED Function
15 July 2019
Welcome back to our regular A to Z of Excel Functions blog. Today we look at the FIXED function.
This function rounds a number to the specified number of decimals, formats the number in decimal format using a period (full stop, “.”) and commas, and returns the result as text.
The FIXED function employs the following syntax to operate:
FIXED(number, [decimals], [no_commas]).
The FIXED function has the following arguments:
- number: this is required and represents the number you wish to round and convert to text
- decimals: this is optional and represents the number of digits to the right of the decimal point
- no_commas: this is also optional. This is a logical value that, if TRUE, prevents FIXED from including commas in the returned text.
It should be noted that:
- numbers in Microsoft Excel can never have more than 15 significant digits, but decimals may be as large as 127
- if decimals is negative, number is rounded to the left of the decimal point (e.g. 10’s, 100’s, …)
- if you omit decimals, it is assumed to be 2 (not zero)
- if no_commas is FALSE or omitted, then the returned text includes commas as usual
- the major difference between formatting a cell containing a number by using a command (e.g. on the ‘Home’ tab, in the ‘Number’ group, click the arrow next to ‘Number’, and then click ‘Number’) and formatting a number directly with the FIXED function is that FIXED converts its result to text. A number formatted with the ‘Cells’ command is still a number.
Please see my example below:
We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.
A full page of the function articles can be found here.