MrExcel.com - Your One Stop for Excel Tips & Solutions

Why Is This Price Showing $27.85000001 Cents?

March 01, 2022 - by

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.

A simple formula of (43.1-43.2)+1 should be 0.9. But Excel shows it as 0.89999999999999
Figure 350. 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.

Format the cell containing the tiny error with 2 decimal places. Now it shows 0.90.
Figure 351. 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.

Although the cell is showing 0.90, it is not equal to 0.90, as a simple formula of =D1=0.9 returns FALSE.
Figure 352. 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.

File, Options, Advanced. Scroll to When Calculating This Workbook. Choose the selection for Set Precision as Displayed.
Figure 353. 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.

When using Precision as Displayed, Excel warns you Data Will Permanently Lose Accuracy.
Figure 354. This warning displays while in the Options dialog.

This article is an excerpt from Power Excel With MrExcel

Title photo by David Clode on Unsplash