After closer inspection, we figured out that because the computer's region was set to "English (United Kingdom)" it was not treating the "$" symbol as currency. It was treating is as text, resulting in broken formulas. I assumed I could simply select and format these cells as "Currency", "$ English (U.S.)", but that doesn't change anything.
Here is the workaround that we came up with (to replicate, you must set region to UK):
- Create an Excel file with dollar values and a formula that will sum the cells. You can see that they are being treated as text, since they are aligned to the left and the formula result is "0".
- Select the cells with the dollar values.
- Press Control-F to open the "Find & Replace" feature.
- Click the "Replace" tab
- In "Find what" enter "$" and in "Replace with" make sure it is blank.
- Click "Replace All"
- The formula will now calculate properly, but we need this to display as currency.
- Select the cells.
- Right-click and select "Format Cells..."
- Click "Currency" on the left. In the right pane, select "Symbol", "$ English (U.S.)".
- Click OK
- Values will now display properly with US dollar symbol and the formula calculates properly!
