Excel Function: DOLLAR
Purpose
The DOLLAR function transforms a numeric value into a text value and formats it according to both the number & currency formatting rules set up on the computer on which it is being used. Numeric operations cannot be performed on the output of DOLLAR because it is always a text value. The DOLLAR function has 4 downsides and because of them the use of the DOLLAR function is not recommended.
- Formatting rules will vary between computers, meaning the output of DOLLAR will as well.
- The rules that affect the format of DOLLAR’s output are not what one would expect them to be.
- Modifying formatting rules to achieve a specific effect will likely impact other software.
- Its name causes confusion because DOLLAR is no more strongly connected with the US Dollar than any other currency.
The TEXT function should be favoured when numeric values must be transformed into text values and formatted as a currency.
Invocations of the DOLLAR function take the form:
DOLLAR(number, [decimals])Example
Consider a cell containing the formula:
=DOLLAR(123456789.12,3)The formula instructs the DOLLAR function to change the number “123456789.12” to have 3 digits following its decimal point, convert it to a text value, and format it according to the number & currency formatting rules on the computer on which the function is being used. To predict what the output will be, the formatting rules which affect the output of DOLLAR must first be understood. The following settings affect the formatting applied to a value by DOLLAR:
| Formatting rule type | Formatting option | Purpose |
|---|---|---|
| Number | Decimal symbol | Defines the character used by DOLLAR to represent a number’s decimal point. |
| Number | Digit grouping symbol | Defines the symbol used by DOLLAR to represent digit grouping symbols. |
| Currency | Digit grouping | Defines the digit grouping pattern followed by digit grouping symbols. |
| Currency | Currency symbol | Defines the currency symbol that DOLLAR adds to the output. |
| Currency | Positive currency format | Defines the format of DOLLAR’s output when it is applied to numbers ≥ 0. |
| Currency | Negative currency format | Defines the format of DOLLAR’s output when it is applied to numbers < 0. |
The diagram below demonstrates that the output varies depending on the values assigned to the number & currency formatting options & because of this, one cannot expect the same results to be generated on different computers by formulae leveraging the DOLLAR function.
Arguments
| Argument | Argument type | Description |
|---|---|---|
| number | Mandatory | The numeric value that will be converted to a text value. |
| decimals | Optional. Defaults to 2 if omitted. | If a positive integer is supplied, it dictates the number of digits which appear in the result’s mantissa. If a negative integer is supplied, it dictates the number of digits to the left of the result’s decimal point which will be set to zero. Consider a situation in which number had a value of 12345 and decimals had a value of -3. the result would be “12000”. However, if decimals had a value of +3, the result would be “12345.000”. |
Related Functions
| Function | Example | Purpose |
|---|---|---|
| TEXT | TEXT(value,format_text) | Converts a value of any type into text and applies a format code to it. |
Error Conditions
| Error value | Condition type | Condition |
|---|---|---|
| #VALUE! | Runtime | A non-numeric value was supplied to the number argument. |
Examples
| Example | Formula | Result | Description |
|---|---|---|---|
| 1 | =DOLLAR(123456789.12,3) | £123,456,789.120 | Transform a number greater than one into a text value, put 3 digits in the result’s mantissa and format it per the computer’s number & currency formatting rules. |
| 2 | =DOLLAR(123456789.12,-3) | £123,457,000 | Transform a number greater than one into a text value and, by supplying a negative number to the decimals argument, remove its mantissa and set the 3 least significant digits to the left of its decimal point to zero (0). |
| 3 | =DOLLAR(0.123456789,3) | £0.123 | Transform a number between zero and one into a text value, put 3 digits in the result’s mantissa and format it per the computer’s number & currency formatting rules. |
| 4 | =DOLLAR(0.123456789,-3) | £0 | Transform a number between zero and one into a text value and, by supplying a negative number to the decimals argument, remove its mantissa and set the 3 least significant digits to the left of its decimal point to zero (0). |
| 5 | =DOLLAR(-123456789.12,3) | -£123,456,789.120 | Transform a number less than minus one into a text value, put 3 digits in the result’s mantissa and format it per the computer’s number & currency formatting rules. |
| 6 | =DOLLAR(-123456789.12,-3) | -£123,457,000 | Transform a number less than minus one into a text value and, by supplying a negative number to the decimals argument, remove its mantissa and set the 3 least significant digits to the left of its decimal point to zero (0). |
| 7 | =DOLLAR(-0.123456789,3) | -£0.123 | Transform a number between zero and minus one into a text value, put 3 digits in the result’s mantissa and format it per the computer’s number & currency formatting rules. |
| 8 | =DOLLAR(-0.123456789,-3) | £0 | Transform a number between zero and minus one into a text value and, by supplying a negative number to the decimals argument, remove its mantissa and set the 3 least significant digits to the left of its decimal point to zero (0). |
Examples: Errors
| Example | Formula | Result | Description |
|---|---|---|---|
| 1 | =DOLLAR("a") | #VALUE! | Supplying a non-numeric value to the number argument causes the #VALUE! error. |