IF comparison not returning correct value

[Geeta700]

Hi

Could somebody pls help me see what I am doing wrong here.  I want to compare the contents of cell O5 with the contents of Z40. If its the same return "balanced" if not return "error". 

The contents of O5 is calculated as  =O4-O3

The contents of Z40 is calculated as =Y40-X40

I can see that O5 equals Z40, but the formulae returns "error". 

I have tested the function as follows and it works.

So why doesn't it work where I really need it to work?

[#2]

@Geeta700 

Probably a rounding error.

Change the formula to

=IF(ROUND(O5,2)=ROUND(Z40,2),"balanced","error")

[#3]

@Geeta700 

Without sample file it's hard to say something concrete. I may only guess that's so called floating point error. Try to compare ROUND(O4-O3, 5) and ROUND(Y40-X40, 5) or like.

[#4]

@Geeta700  ....  The formulas in O5 and Z40 should be:

=ROUND(O4-O3, 2)

=ROUND(Y40-X40, 2)

Note:   Round to 2 decimal places because (if) that is the accuracy that you expect.  Do not round to an arbitrary number of decimal places like 5.

We have no idea what the exact values are in O3, O4, X40 and Y40.  It is interesting to note that 6+5+7 = 18 -- the values in the table in your posting.  But of course, that is not calculated with those numbers by an expression of the form O3-O4.

The problem might not even be the infamous "floating-point error" problem.  For all we know, what appears to be 18.00 due to formatting in both cells is really 17.9950 in one cell and 18.0049 in another cell due to unrounded calculations elsewhere.

But in general, if you expect a calculation to be accurate to 2 decimal places (for example), explicitly round to that number of decimal places.  Formatting alone affects only the appearance of the result.

(I do not recommend setting the "Precision as displayed" option, for oh-so-many reasons.)

[#5]

Thank you Hans. It worked. Sooo good to have this forum.

[#6]

Thank you Sergei. Yes it was a floating point error. Fixed now.

[#7]

Thank you Joe. I didn't realise that formatting only affected the appearance. Something new learnt today. Used the ROUND function and it worked beautifully. Thank you again for taking the trouble to explain all that.