Excel Why Is This Price Showing $27.85000001 Cents?
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Why Is This Price Showing $27.85000001 Cents?
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.
Figure 356 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:
1. Format your prices to have two decimal places. Use either the Format Cells dialog or the Decrease Decimal icon.
Figure 357 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.
Figure 358 The formatting is showing 0.90, but the cell really isn’t 0.90.
2. 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.
Figure 359 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.
Figure 360 This warning displays while in the Options dialog.