Complex Nested And If Formula Example In Excel

Excel function tutorials

Once in a while I get asked some more complex nested if/and questions. In almost all cases, it is all about defining the problem in a simple way and building the formula step by step. Here is one question I received a couple of weeks ago:

If I redefine this problem. I have 4 values. In order for the entire problem to be true, 2 conditions must be met. And each of those conditions can be accomplished with 1 of 2 possible values. So let’s go ahead and try this out:

I will start at the end. If A=C or C=D true, that condition will be met. Here we go:

=IF(OR(C2=C4,C4=C5),1,0)

Basically, if one of the conditions is met, the formula will give me 1. I will do the same thing for that other part.

=IF(OR(C2=C3,C3=C5),1,0)

Now, I must combine these 2. Basically, if the sum of both is 2, then the overall condition is met. If not, it is false. Here is how I’ll do it:

=IF(IF(OR(C2=C4,C4=C5),1,0)+IF(OR(C2=C3,C3=C5),1,0)=2,”True”,”False”)

You can see the result which is false of course:

If I change B and C to match A, I should get a “true”

And here I go:

Of course, this could be done in many different ways but I just wanted to show another way. Hopefully this helps!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

3 Feedbacks on "Complex Nested And If Formula Example In Excel"

Bad Debt

fantastic publish, very informative. I ponder why the opposite experts of this sector do not understand this.

You must proceed your writing. I’m confident, you have a great readers’ base already!



Phyllis

I read this piece of writing completely regarding the
comparison of most recent and preceding technologies, it’s awesome article.



ramuan herbal

Why visitors still use to read news papers when in this technological globe everything is available on net?