Microsoft Excel: Why Is This Price Showing $27.85000001 Cents?

Problem: You have a worksheet in which you expect the cells to show dollars and cents. For some reason, a price in the formula bar is showing a few millionths of a dollar, as shown in Fig. 464.

Strategy: These stray values can happen due to something called floating- point arithmetic. Although you think in tens, computers actually calculate with twos, fours, eights, and sixteens. Excel has to convert your prices to sixteens, 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 these seemingly bizarre rounding errors creep in. There is one quick solution, but you have to be careful when using it.

1) Format your prices to have two decimal places, as shown in Fig. 465.

2) As shown in Fig. 466, from the menu, select Tools – Options – Calculate – Precision as Displayed. Excel will immediately truncate all values to only the number of decimals shown.

Gotcha: There is neither Undo nor any way to regain those last numbers. Excel will warn you that your data will permanently lose accuracy, as shown in Fig. 467.

Summary: If you have annoying floating-point errors in your data, you can turn on Precision as Displayed. You should exercise caution when using this option because it will permanently change the values of all numbers in your workbook.

Commands Discussed: Tools – Options – Calculate – Precision as Displayed