IF Formulas

Hi goneps,

That's a perfectly acceptable use of IF statements to me. I don't believe there is any way around them for that purpose.

goneps said:

I plead guilty to the charge of using a lot of IF statements, but more often than not they are to forestall multiple #DIV/0! errors when the precedents are not filled in. For instance:

=IF(AND(A1=0,C1=0),0,A1+C1)

So how can the boolean method be used in those circumstances? Blowed if if can work it out.

Richard

Click to expand...

I first structured it to a boolean form and, even though I feel it is pointless and wrong, I came up with

=--(NOT(AND(A1=0,C1=0)))*(A1+C1)

But then I looked at your formula and came up with

=A1+C1

OK, Bob—I oversimplified it. I should have used =A1*C1 as an example, since most of my formulas involve multiplication or division, so we're back to the problem of thousands of #DIV/0! errors unless we implement some means of taming them.

Nonetheless, thanks for showing that it IS possible, even though it's not pretty. Because I use this form of IF statement so extensively I've been surprised it hasn't received so much as a mention in any of the articles I've read on the subject.

Would you explain the function of the two hyphens "--" after "="?

Richard

The -- is a method of coercing an expression that resolves to TRUE or FALSE to 1 or 0 instead, so as to get a numeric result.

In this case it is actually redundant, because the * operator will also have the effect of coercing TRUE or FALSE to 1 or 0.

Thanks, Bob—useful to know. R.