0

I have a cell (A40) in an Excel worksheet which contains the sum of a range of values (formula: =SUM(A1:A39)).

Another cell should print "OK" if the sum is 0 and "ERROR" if the sum is not 0. I have the following formula: =IF(A40=0;"OK";"ERROR"). Unfortunately it always prints "ERROR".

A1 to A40 are all in the format "Currency" if that matters.

Is this another "never compare floating points to fixed values, instead use a small epsilon range"-error? The following fix works... =IF(AND(A40>-0,1; A40<0,1);"OK";"ERROR"). How many zeroes are "enough" to always get the correct "OK" or "ERROR" output?

1 Answer 1

2

Yes, you are going to have problems comparing a floating point to an integer. Try =IF(ROUND(A40;2)=0;"OK";"ERROR")

This will first round your sum to two decimal places, then do the comparison.

1
  • Thank you, ROUND is a much better idea than to use a more complicated formula for an epsilon-range comparison!
    – D.R.
    Nov 2, 2015 at 19:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .