confused by rounding error at 14th decimal place

[gemmae]

I've been learning how to use conditional formatting and decided to apply it to my bank account records that I have stored in Excel. I wanted to check for missing sections in my statement in case I lost any transactions when exporting the data in little chunks at my internet banking website.

I built my conditional formatting around a straightforward true/false rule.

= Balance = PreviousBalance minus Debit plus Credit

In a handful of rows this is throwing up a False when balance is correct. After some playing around I discovered that Excel was introducing an error at the 14th decimal place.

When A1 is 67.13, A2 is 72.43, Excel calculates =A1-A2 as -5.3000000000000100000

Some Googling led me to this post at Microsoft Answers. But I don't understand how/where I am supposed to be using the Round function.

[Excel 2007, Windows 7.]

 

[circledchicken]

I'm not quite sure how you want this, but for example if you want that result to just be -5.3, you can use:
=ROUND(A1-A2,2)

 

[konew1]

another way is to change the conditional format to trigger if the formula result is not between -0.0001 and 0.0001

 

[gemmae]

Circledchicken, you'd use Round in the conditional formatting formula rather than with the data itself? I was a bit confused by the following in the MS Answers thread:

As a general rule you want to be carefull using equal with floating point numbers. You rcomputer works in binary and shows the results in decimal. Some numbers don't translate exactly and you can get very small errors which will make what appears to be simple math show a wrong result. You should ALWAYS use a round function to remove those small errors.

HTH... Jim Thomlinson

I have the data in these columns formatted to display two decimal places. I understand that this does not change the number of decimal places in the underlying data. My underlying data contains numbers with 0, 1 or 2 decimal places. Is this the same as "floating point numbers" (in the above quote)?

Does this just mean that when I'm using conditional formatting it's good practice to use Round in the formula just in case Excel throws out a weird rounding error?

My actual formula in the conditional formatting rule is:

=IF($H3=$H2-$F3+$G3,0,1)

Would I just put a Round function on each side of the equals sign? Um....

=IF(ROUND($H3,2)=ROUND($H2-$F3+$G3,2),0,1)

Is this considered best practice/normal?

Konew1, sorry, I don't really understand your reply.

 

[shg]

Or this:

=--(ABS($H3-$H2+$F3-$G3) > 0.0001)

But, in general, most formulas that return currency values should include rounding to two significant digits (cents).

 

[ZVI]

=IF(ROUND($H3,2)=ROUND($H2-$F3+$G3,2),0,1)

Excel recognizes non zero value as TRUE in logical expressions, therefore your CF formula can be simpler:
=ROUND($H3-$H2+$F3-$G3,2)

 

[gemmae]

Excel recognizes non zero value as TRUE in logical expressions

Ahhh. I didn't know that. I had a feeling that my use of the IF function to reverse the effect my formula was having was pretty heavy-handed, but your formula is even simpler again. Thanks.

Or this:

=--(ABS($H3-$H2+$F3-$G3) > 0.0001)

But, in general, most formulas that return currency values should include rounding to two significant digits (cents).

I'll leave the VBA until I've got to that part of my studies.

And I'll remember to use Round in any accounting formulas. I would have understood the need for it if I were dividing or multiplying, but I am still a little freaked out that adding and subtracting (when the input values had no more than two decimal places) was vulnerable to this glitch. My conditional formatting formula was incorrectly triggered nine times in just one worksheet!

 

[shg]

I'll leave the VBA until I've got to that part of my studies.

I didn't post any VBA -- that was an alternative to your CF formula.

 

[gemmae]

I didn't post any VBA -- that was an alternative to your CF formula.

Oh, for goodness sake. I think it was the double minus sign at the front that freaked me out. Plus when I went to find out what ABS was, I typed it in the "search for a function" box and got no results.

Now that I go back and browse the list of all functions underneath, I see it's at the very top! Curses.

I still don't actually understand it entirely though.

=--(ABS($H3-$H2+$F3-$G3) > 0.0001)

So.... if my bank statement balances it would give either zero, or plus or minus 0.00000000000001, depending on if the glitch occurred. ABS would get rid of the "plus or minus" issue. > 0.0001 would strip out the rounding error (and not strip out instances where my balance was even one pence wrong), and return a FALSE for balanced, and a TRUE for not balanced. What then is the purpose of the double negative at the front?

 

[MARK858]

The double negative is just to change True to 1 and False to 0 so they can be used in future calculations