Why is excel adding decimals to whole numbers using simple subtraction?

Question

When doing very simple subtraction EXCEL is adding decimals to the results.  This is making verification impossible.

Here is example:  $7,959.1 (yes we are only showing 1 decimal place) MINUS $8,744.9= $785.8 should be the result, but instead we get

785.799999999999999999 can anyone explain this?  How do I stop it from happening.  My verification requires 785.8, I get errors from 785.7999999 even thought the OUTPUT shows 785.8

Dilip Rao · Accepted Answer

Only formatting the cell wont work.

Use the "Round" function to force the decimal to one digit e.g. =Round(A1-B1,1).

Or else you may format the cell to show only one decimal place, and then use the "Precision as displayed" option. (Tools -- Options -- Calculation Tab)

bigtalltom · Answer

It is probably the result of the binary to base-10 number conversion.

All computer logic is done in binary, when converting binary to the standard base-10, sometimes it can get a little off like that when dealing with decimals

If you format the cell to show just 1 decimal point, the display will be 785.8

maniacmartinuk · Answer

As the previous answer said. Click Format > Cells. Select currency or number and you can set decimal places here

Devin&#39;s mom · Answer

format your cells to "number" and adjust the number of decimals you want to use.  If you click on the column header, then click format cells, you will change all of the cells in the column.

excel 2007 has a section "number" in the tool bar where this can be adjusted.

Trending News

Why is excel adding decimals to whole numbers using simple subtraction?

4 Answers