DOLLAR

What it does

DOLLAR(number, [decimals])

DOLLAR rounds a number to a given number of decimal places and returns it as text, formatted as currency. Microsoft's article names the format applied: $#,##0.00_);($#,##0.00) — thousands separated, two decimals by default, negatives in parentheses — and states that the currency symbol actually used depends on your language settings.

Decomposed, the operation is:

1. convert the argument to a number by the ordinary rules;
2. round it to decimals places — with negative decimals rounding to the left of the decimal point, so -1 rounds to the nearest ten;
3. render the rounded value through the locale's currency format, including symbol placement, grouping separator, decimal separator, and the negative-number convention, which follows the sign of the input even when the value rounds to zero (FN-DOLLAR-001);
4. return the rendering as Text.

Step 3 is not a pure function of the arguments, and no argument can override it.

Arguments

number — required. The article describes it as a number, a reference to a cell containing a number, or a formula that evaluates to a number.

decimals — optional, the number of digits to the right of the decimal point. Defaults to 2 when omitted, per the article. A negative value rounds the number to the left of the decimal point.

Two positions that get misread:

• decimals controls rounding, not just display. DOLLAR(1234.567, -1) is not 1234.567 displayed coarsely; the value rendered has been rounded first, and the digits are gone.
• Whether a non-integer decimals is truncated, rounded, or rejected is not stated in the article and has not been checked here.

Try it

Quirks

Result and edge cases

The return kind is Text. That is the most consequential fact on this page, and Microsoft's article puts a warning box around it: numbers stored as text are a common cause of spreadsheet errors, because many functions ignore them — SUM, AVERAGE, MIN, MAX. The article's own advice is to use the Format Cells dialog or the Accounting Number Format instead, which keeps the value numeric.

The Handbook's coercion chapter explains why the trap is asymmetric and therefore hard to notice: SUM(DOLLAR(5)) as a direct argument coerces the text back to a number and gives 5, while the same text sitting in a cell and reached through a range scan is ignored and contributes 0. The same string, the same function, two answers, decided by how the value arrived (chapter 02).

Other boundaries:

• Zero and negatives render through the format's negative branch, which is parenthesised in the documented US format and is a leading minus in the recorded en-ZA settings, where DOLLAR(-0.005,0) is -R0 (FN-DOLLAR-001). This is a locale-visible difference in the shape of the output, not only in the symbol.
• An empty cell as number converts to zero under the ordinary numeric coercion, so DOLLAR of a blank is a rendering of zero rather than empty text.
• Very large magnitudes must eventually exceed what a fixed-decimal rendering can express; where that boundary lies, and whether it produces an error, is not documented in the article.

Errors

The article does not enumerate error conditions. The reachable errors are the shared ones: an error value in either argument propagates, and non-numeric text as number fails to-number conversion and surfaces #VALUE! under the shared coercion rules (chapter 02).

Because step 3 above involves a host facility, a fourth failure mode exists in principle — a locale profile that cannot render the requested combination — with no documented worksheet-visible behaviour. Nothing about it has been observed here.

How to implement

Notes for implementers

1. The locale profile is an input. An implementation that hard-codes $ and , is implementing the en-US instance of this function, not the function. State the profile as part of the result's scope, exactly as an Excel build and platform would be.
2. Round before rendering, once. Rounding inside the formatter and rounding beforehand can differ at ties; the documented behaviour is a rounding step with an explicit decimals argument, so keep it explicit.
3. Negative decimals is a rounding magnitude, not a formatting flag, and must still render with the format's decimal count — FIXED(1234.567,-1) is documented as 1,230, and the analogous DOLLAR case is worth pinning by probe rather than by analogy.

Rust code

4 dollar_fn "DOLLAR" (1, 2) [V "number", V "decimals"] |s, a, sv| { dollar(s, a) }

fn dollar(site: &Site<'_>, a: &[CalcValue]) -> Step<CalcValue> {
    let (_, profile) = profile(site)?;
    let number = num(site, a, 0)?;
    // W111 locale-profile 00193: unlike FIXED, an explicit placeholder means zero places.
    let places = if a.get(1).is_some_and(CalcValue::is_missing) { 0 } else { places(site, a)? };
    rendered(site, format::dollar(profile, number, places))
}

/// DOLLAR: the number in the locale's currency format, rounded to `places`.
pub(crate) fn dollar(profile: &FormatProfile, value: f64, places: i32) -> Rendered {
    let magnitude = fixed(Some(profile), value.abs(), places, false)?;
    let spacing = match profile.currency_spacing {
        CurrencySpacing::None => "",
        CurrencySpacing::Space => " ",
        CurrencySpacing::NarrowNoBreakSpace => "\u{202F}",
    };
    let symbol = profile.currency_symbol;
    let body = match profile.currency_placement {
        CurrencyPlacement::Before => format!("{symbol}{spacing}{magnitude}"),
        CurrencyPlacement::After => format!("{magnitude}{spacing}{symbol}"),
    };
    let negative = value < 0.0 && (places >= 0 || magnitude.chars().any(|c| c.is_ascii_digit() && c != '0'));
    if !negative {
        return Ok(body);
    }
    Ok(match profile.currency_negative_pattern {
        CurrencyNegativePattern::LeadingMinus | CurrencyNegativePattern::MinusBeforeSymbol => {
            format!("-{body}")
        }
        CurrencyNegativePattern::TrailingMinus => format!("{body}-"),
        CurrencyNegativePattern::Parentheses => format!("({body})"),
    })
}

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

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• FIXED is the near-twin: same rounding-then-render-as-text shape, no currency symbol, and an explicit no_commas switch that DOLLAR does not have. If you want grouped decimal text without a currency symbol, FIXED is the function. See FIXED.
• TEXT is the general case, taking an arbitrary format string; DOLLAR is TEXT with the currency format baked in and the format argument replaced by a decimals count. The two differ on an amount that rounds to zero: DOLLAR keeps the minus sign, TEXT drops it (FN-DOLLAR-001).
• VALUE and NUMBERVALUE are the inverse direction, parsing formatted text back to a number — and NUMBERVALUE exists precisely because the inverse is locale-dependent too.
• DOLLARDE and DOLLARFR share the word but not the job: they convert between fractional and decimal notations of a price and return numbers, not text. They are unrelated to this function despite the name collision, and readers confuse them regularly.
• Cell number formatting is the non-function alternative the documentation recommends.

Named variants

No named variants are recorded yet.