FIXED

What it does

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

FIXED does three things in one call, and Microsoft's article states them in order: it rounds a number to the specified number of decimals, formats it in decimal format using a period and commas, and returns the result as text.

Written out:

1. convert the argument to a number;
2. round to decimals places, where a negative decimals rounds to the left of the decimal point (the article's own example: FIXED(1234.567, -1) is 1,230);
3. render with exactly decimals digits after the decimal separator — padding with zeros if the value has fewer — and with thousands grouping unless no_commas is TRUE;
4. return Text.

Step 3 is what distinguishes FIXED from ROUND: ROUND(1.5, 2) is the number 1.5, while FIXED(1.5, 2) is the text 1.50. The trailing zero is the point of the function.

Arguments

number — required, the value to round and convert to text.

decimals — optional, the number of digits to the right of the decimal point. Defaults to 2 per the article. Negative values round to the left of the decimal point.

no_commas — optional logical. When TRUE, the returned text contains no thousands separators; commas appear by default. Note the polarity: the argument is a suppression flag, so the default FALSE means "do include grouping". This is the argument position readers most often invert.

The article also records a precision remark worth carrying: Excel numbers hold at most 15 significant digits, but decimals may be as large as 127. The two facts together mean a large decimals cannot conjure information — beyond the 15 significant digits available, the extra places render whatever the binary double actually is, not the decimal literal the user typed.

Try it

Quirks

Result and edge cases

The return kind is Text.

• Padding is guaranteed. FIXED(44.332) is documented as 44.33; a value with fewer decimals is padded rather than shortened.
• The result is text, and the article says so in contrast with cell formatting: formatting a cell through the Cells command leaves the value a number, while FIXED converts its result to text. The same downstream trap applies as for DOLLAR — a range scan by SUM or AVERAGE ignores numeric-looking text, while the same text passed as a direct argument coerces back to a number (chapter 02).
• Negative decimals beyond the magnitude of the number must round the value to zero. How the sign of a negative input survives that is not documented, and no negative decimals has been checked here, but at decimals 0 the sign is dropped: FIXED(-0.005,0) is 0 (FN-DOLLAR-001).
• Empty cells, omitted arguments and error inputs follow the shared call model.

Errors

The article enumerates no error conditions. Reachable errors are the shared ones: an error value in any argument propagates, and non-numeric text as number surfaces #VALUE! under the shared coercion rules.

decimals is documented as admitting values up to 127; what happens beyond that, and what happens for a very large negative decimals, is not stated.

How to implement

Notes for implementers

1. Round once, render deterministically. Round-then-format and format-with-precision can differ at ties and at values that are not exactly representable; the documented behaviour has an explicit rounding step, and it should be a real, separate step.
2. Grouping is locale structure, not a comma. Group size is not universally three, and the separator is not universally a comma. no_commas should suppress grouping, whatever the grouping convention is.
3. The 15-significant-digit remark is a real constraint on tests. A vector suite built from decimal literals with more than 15 significant digits is testing the parser, not FIXED.

Rust code

3 fixed_fn "FIXED" (1, 3) [V "number", V "decimals", V "no_commas"] |s, a, sv| { fixed(s, a) }

fn fixed(site: &Site<'_>, a: &[CalcValue]) -> Step<CalcValue> {
    let number = num(site, a, 0)?;
    let places = places(site, a)?;
    let no_commas = a.len() > 2 && flag(site, a, 2)?;
    let profile = site.environment().and_then(|environment| environment.profile.as_ref());
    rendered(site, format::fixed(profile, number, places, no_commas))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

The recorded environments below distinguish a reading from a same-input comparison. Counts describe captured inputs in each environment; overlapping captures and comparisons are not totals of unique worksheet calls.

The record contains no same-input comparison between these environments. Reading different inputs on different builds does not establish either a version change or cross-build agreement.

Recorded environments and comparison details.

Alternatives

Relationships

• DOLLAR is the currency-symbol counterpart: same rounding-then-text shape, plus a symbol, and without the no_commas switch. See DOLLAR.
• TEXT is the general form; anything FIXED produces can be produced by TEXT with an appropriate format string, at the cost of writing the format string.
• ROUND performs step 2 alone and returns a number. FIXED is not a rounding function that happens to return text — it is a rendering function whose rounding you can specify, and the distinction matters because the result cannot be arithmetic on afterwards without conversion.
• VALUE / NUMBERVALUE invert it, locale permitting.
• Excel's Fixed Decimal entry option (Advanced settings) shares the word and has nothing to do with this function.

Named variants

No named variants are recorded yet.