Get help with an IF and AND formula

You're almost there.

The two key parts of your formula are the IF() and AND() statements.

I find it helps to break them out into multiple lines (using option-return within the formula editor).

IF() takes three parameters:

=IF(test_condition,
    value_if_true,
    value_if_false
)

We know what the if_true and if_false values are, so they're easy to set:

=IF(test_condition,
    10+C3,
    C4
)

so now we're only left with the test_condition.

In this case, you want three things to be TRUE - C3 is not blank; C4 is not blank; C3=C4. For this we use an AND() statement. What many people don't realize is that AND() can compare any number of conditionals, not just two, so we can just string them together:

=IF(AND(

        NOT(ISBLANK(C3)),

        NOT(ISBLANK(C4)),

        C3=C4),

    10+C3,

    C4)

Now if three checks pass (return TRUE), the result will be 10+C3. If any of them fail, it returns C4.

is that what you wanted?

Update:

In thinking this through a little more, I found a use case that may break the model.

If either C3 or C4 are blank, my formula returns the value in C4. Reading between the lines in your original post I see that may not be what you want. I'm guessing you want nothing if either C3 or C4 are blank?

If so, my formula needs a little rework, namely two distinct IF() statements - one for checking for blanks (returns empty), and one for checking C3/C4 equality.

If that's what you want, just realize that either the if_true of the if_false parameters can be another IF() statement.

So logically, I think you may want:

IF C3 is blank or C4 is blank return an empty value, otherwise check if C3 and C4 are equal and return either 10+C3 or C4, accordingly.

That can be written two ways, like:

=IF(OR(
    ISBLANK(C3),

    ISBLANK(C4)),

    "",

    IF(C3=C4,

        10+C3,

        C4)

    )

This can be read as "If either ISBLANK(C3) or ISBLANK(C4) returns TRUE then return "", otherwise, check if C3=C4 and return 10+C3 if TRUE, or C4 if FALSE.

If you find yourself getting twisted up in the nesting of logic in a formula you can "flatten it out" into simple ifs-expression, ifs-true pairs using the new(ish) IFS function specifically designed for that purpose.

=IFS(ISBLANK(C3),"",ISBLANK(C4),"",C3=C4,10+C3,C3<>C4,C4)

This works from left to right. In plain English the formula does something as follows.

Check if C3 is blank.

If C3 is blank then enter the null character in the cell that contains the formula and stop.

If it is not blank ten move on to the next ifs-expression that checks if C4 is blank.

If C4 is blank enter the null character and stop.

If C4 is not blank then go on to the next ifs-expression.

If C3=C4 then enter 10 +C3 and stop, otherwise move on to the next ifs-expression.

If C3≠C4 then enter C4.

The problem is thus reduced to a mechanical sequence of simple comparisons. You don't have to wrestle with OR and AND and all that.

SG

Hi bobvawter,

Not enough details to help us help you.

Please post a screen shot that includes the column letters and the row numbers.

For example, is this how your table looks?

And is the formula in D3?

More information about your overall aim may lead to a solution.

Regards,

Ian.

That's a good point using IFS()

I was more focussed on converting/upgrading my original standard IF() to catch the use case of a missing value, and didn't think about how IFS()s might be more straightforward.