Ok, I have a fairly complexe spreadsheet setup for a personal budget.

I've got data being pulled, compared, and discriminated from two other sheets to fill the fields on the main balance sheet.

I decided that as time goes on that I'm going to take older rows and do a copy & paste Values on the cells to limit the load on the system since so many of the cells are formula driven.  So I went to do this on about the first 55-60 rows of data when I noticed something rather odd.  In the main account balance columns, who's formulas look like this, =H45+M46+P46-S46, that I was occasionally getting values (after the paste) that read, for example 65.3499999999999.  So I went back to start looking at formulas to see if this was resulting from the Copy and Paste or if this was the result of of a math error.

Going the last location in which I found a whole number result from the copy and past and doing an Evaluate Formula yields this: =126.7+0+0-64.5 substituting the values into the formula.  When I do an Evaluate formula the final result comes back as 62.20, which is correct, and no where during the evaluation do I ever see any extra decimals or non-zero values displayed.  But when I do the Evaluate Formula on the next cell down, which pulls it's value from the cell above it, it shows up like this =62.1999999999999+0+0-0.  Now the 126.7 and 64.5 are hard entered values, NOT formula generated values.  The two 0 values are formula generated values but are 0s from conditional expressions not from calculations i.e. the result of an IF(X,Some formula,0) formula evaluating FALSE.

Can anyone explain what the heck is going on here?  I mean I realize that this is a very tiny error, but if I were to go forward using this that error could eventually start causing problems if it propagates throught the rest of the table as there are several places where I am calculating interestes and other things that are multiplicive.

In fact interestingly enough I just discovered another odd one.  So the first value, H28, is the cell above's value, generated by the same formula (basically a rolling balance calculation) and the value above is 0.25.  The next value, M29, is through a look up table and  from there is calculated from the difference between two hard entered values which results in 118.15.  The third value, P29, is hard entered and is -118.  The forth value, S29, is hard entered and is a 0.  So here are the calculation steps that Excel shows me...  For cell H29:

=H28+M29+P29-S29
=0.25+M29+P29-S29
=0.25+118.15+P29-S29
=118.4+P29-S29
=118.4+(-118)-S29
=0.399999999999977-S29
=0.399999999999977-0
=0.4

I don't know if that helps, but I'm at a loss to explain it.  What's really weird is that rows later the extra decimals disappear, then reappear, then disappear, and then reappear several times....

We humans use decimal numbers. Excel stores numbers in binary format. Most "finite" decimal numbers cannot be represented exactly as binary numbers with a finite number of digits, so tiny rounding errors occur. This is unavoidable.

In many cases, Excel suppresses these rounding errors, but sometimes it doesn't.

If necessary, you can use the ROUND function to round the result of a calculation or even of its intermediate steps to a fixed number of decimal places.

Hi L_C_R_ANT,

Based on your description, floating-point arithmetic may give inaccurate results in Excel.

Please refer to this KB article for explanations:
https://support.microsoft.com/en-us/kb/78113

As Hans said, you can use ROUND formula to avoid errors in Excel.

Hope it's helpful.

Regards,

Emi Zhang
TechNet Community Su