WD2002: Formula Calculates to Zero When You Use Currency Symbol (274004)

SYMPTOMS

When you attempt to add, subtract, or multiply currency values, the result is incorrectly calculated as zero. For example, if you have the following data in a table, and the currency symbol follows the value, the result may be zero:

12.50$
10.00$
{=SUM(ABOVE)}

However, the reverse is also true. If the currency symbol precedes the value, which is the default currency format for English (United States), the result may also be calculated as zero.

NOTE: When you try to use a formula to divide currency values, the result is returned in the form of the following error message:

!Zero Divide

CAUSE

This problem occurs when the currency symbol format that is specified in the Regional Options (Microsoft Windows 2000) or Regional Settings (Microsoft Windows 98 or Microsoft Windows Millennium Edition [Me]) in Control Panel is different from the format that you are using in your document. By default, Windows uses a format in which the currency symbol, in most cases, precedes the value (for example, $1.1).

Most likely, you see this behavior if you do not modify the currency symbol format when you change the language that you want to use. For example, you change the regional settings to another country/region, such as Germany or France (whose currency symbol follows the value by default), but you do not use the same currency symbol format in both your document and the Windows Regional Options (Windows 2000) or Regional Settings (Windows 98 or Windows Me).

RESOLUTION

Set the currency symbol format in the Regional Options (Windows 2000) or Regional Settings (Windows 98 or Windows Me) in Control Panel. To do this, follow these steps:

Microsoft Windows 2000:

Click Start, point to Settings, and then click Control Panel.
Double-click Regional Options.
Click the General tab.
Change the Your locale (location) box to the appropriate country/region.

For example, change the Your locale (location) box to English (United States).
Click the Currency tab.
In the Currency symbol box, type or select the currency symbol that you want to use.

For example, type or select $.
In the Positive Currency Format box, select the format that you want for positive values. For example, do one of the following:
- If you want the currency symbol after the value (as in the example shown in the "Symptoms" section of this article), select 1.1$.
  
  -or-
- If you want the currency symbol before the value, select $1.1.
In the Negative Currency Format box, select the format that you want for negative values. For example, do one of the following:
- If you want the currency symbol after the value (as in the example shown in the "Symptoms" section of this article), select (1.1$).
  
  -or-
- If you want the currency symbol before the value, select ($1.1).
Click Apply.
Click OK.

Microsoft Windows 98 or Microsoft Windows Me:

Click Start, point to Settings, and then click Control Panel.
Double-click Regional Settings.
Change the Regional Settings to the appropriate country/region.

For example, change the Regional Settings to English (United States).
Click the Currency tab.
In the Currency symbol box, type or select the currency symbol that you want to use.

For example, type or select $.
In the Position of currency symbol box, select the format that you want. For example, if you want to place the currency symbol before the value, select 1.1 or 1.1.
In the Negative Format box, select the format that you want. For example, if you want to place the currency symbol before the value, select - 1.1 or -1.1.
Click Apply.
Click OK.