Problem: I have a worksheet in which I expect the cells to show dollars and cents. For some reason, a price in the formula bar is showing a few millionths of a cent.
- Not quite 0.90.
Strategy: These stray values can happen due to something called floating-point arithmetic. Whereas you think in 10s, computers actually calculate with 2s, 4s, 8s, and 16s. Excel has to convert your prices to 16s, do the math, and then present it to you in tenths. A simple number like 0.1 in a base-10 system is actually a repeating number in binary.
Sometimes seemingly bizarre rounding errors creep in. There is one quick solution, but you have to be careful when using it:
- Format your prices to have two decimal places. Use either the Format Cells dialog or the Decrease Decimal icon.
- Still not 0.90.
Things now look OK, but if you ever test to see if this value is really 0.90, it will return FALSE.
- The formatting is showing 0.90, but the cell really isn't 0.90.
- Select File, Options, Advanced. In the Calculation Settings For This Workbook section, select Set Precision as Displayed. Using this setting, Excel will truncate all values to only the number of decimal places shown.
- Eliminate the tiny floating point errors.
Gotcha: There is neither an Undo command nor any other way to regain those last numbers. However, Excel will warn you that your data will permanently lose accuracy.
- This warning displays while in the Options dialog.