FIXED Function

Description

The FIXED Function rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

Syntax

FIXED(number, [decimals], [no_commas])

Parameters

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.
No_commas Optional. A logical value that, if TRUE, prevents FIXED from including commas in the returned text.

Remarks

  • Numbers in Microsoft Excel can never have more than 15 significant digits, but decimals can be as large as 127.
  • If decimals is negative, number is rounded to the left of the decimal point.
  • If you omit decimals, it is assumed to be 2.
  • 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 (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.

Examples

The example may be easier to understand if you copy A1:C11 to a blank worksheet.

1
2
3
4
5
6
7
8
9
10
11
A B C
Formula Result Description
=FIXED(1234.567,1) 1,234.6 Rounds the number 1 digit to the right of the decimal point, with commas.
=FIXED(1234.567,1,0) 1,234.6 Rounds the number 1 digit to the right of the decimal point, with commas.
=FIXED(1234.567,1,1) 1234.6 Rounds the number 1 digit to the right of the decimal point, without commas.
=FIXED(1234.567,,) 1,234.57 Rounds the number 2 digit to the right of the decimal point, with commas.
=FIXED(1234.567) 1,234.57 Rounds the number 2 digit to the right of the decimal point, with commas.
=FIXED(1234.567,,1) 1234.57 Rounds the number 2 digit to the right of the decimal point, without commas.
=FIXED(1234.567,-1) 1,230 Rounds the number 1 digit to the left of the decimal point, with commas.
=FIXED(1234.567,-1,1) 1230 Rounds the number 1 digit to the left of the decimal point, without commas.
=FIXED(1234.567,-1,0) 1,230 Rounds the number 1 digit to the left of the decimal point, with commas.
=ISTEXT(FIXED(1234.567,1)) TRUE FIXED function returns the result as text.

Video training

See also: Excel text functions

ASC
Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
BAHTTEXT
Converts a number to text, using the ß (baht) currency format
CHAR
Returns the character specified by the code number
CLEAN
Removes all nonprintable characters from text
CODE
Returns a numeric code for the first character in a text string
CONCATENATE
Joins several text items into one text item
DOLLAR
Converts a number to text, using the $ (dollar) currency format
EXACT
Checks to see if two text values are identical
FIND, FINDB
Finds one text value within another (case-sensitive)
FIXED
Formats a number as text with a fixed number of decimals
WIDECHAR
Changes half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
LEFT, LEFTB
Returns the leftmost characters from a text value
LEN, LENB
Returns the number of characters in a text string
LOWER
Converts text to lowercase
MID, MIDB
Returns a specific number of characters from a text string starting at the position you specify
PHONETIC
Extracts the phonetic (furigana) characters from a text string
PROPER
Capitalizes the first letter in each word of a text value
REPLACE, REPLACEB
Replaces characters within text
REPT
Repeats text a given number of times
RIGHT, RIGHTB
Returns the rightmost characters from a text value
SEARCH, SEARCHB
Finds one text value within another (not case-sensitive)
SUBSTITUTE
Substitutes new text for old text in a text string
T
Converts its arguments to text
TEXT
Formats a number and converts it to text
TRIM
Removes spaces from text
UPPER
Converts text to uppercase
VALUE
Converts a text argument to a number

Leave a Reply

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