How to create complex nested If statement in Excel?

Question

I have an If formula in Excel as seen below:

=IF((Q2="O")*AND(R2="O")*AND(S2="O")*AND(T2="O")*AND(U2="O"),"O","X")

Basically, if cells Q2 to U2 is O the cell with formula will have O written in it. Otherwise it will have X. Now I want to change it into a nested If statement due to new conditions.

These conditions, in order are:

If any one of cells Q2 to U2 = X, cell = X
If any one of cells Q2 to U2 = date format, cell = ∆
If Q2 to U2 = O, cell = O
If none of the conditions are met, the cell will have value "FALSE". (Default appears)

Each of the cells have this condition to follow,

If any one of cells Q2 to U2 = -, ignore that cell and count the other cells to get final result.

I tried switching to Or in my original formula to test out conditions 1 and 3.

=IF((Q2="O")*or(R2="O")*or(S2="O")*or(T2="O")*or(U2="O"),"O","X")

But it doesn't work. Plus I'm not sure how to do condition 5 as well. Any help?

Is it possible to do something so complex just by using Excel formula? Or do I need to go into VBA?

What do you mean by 'count' for condition 4? Do you mean get the number of cells without -? — Jerry
– Jerry, Commented Dec 12, 2017 at 7:05
meaning ignore the cells with - and get final result. Example if U2 = -, carry out condition 1,2 or 3 with cells Q2 to T2. — hjh93
– hjh93, Commented Dec 12, 2017 at 7:09
Simple answer: Don't. Nested IFs tend to give lots of problems, especially if you (or somene else) wants to maintain the document later on. — Peter Pesch
– Peter Pesch, Commented Dec 12, 2017 at 7:10
Longer answer: If the business rule is this complex, some business users will want to know which part of the rule lead to the result. Therefore, it might be a good idea to split it up, and put temporary results (with business value) in extra cells. Bonus effect is that maintenance gets easier. — Peter Pesch
– Peter Pesch, Commented Dec 12, 2017 at 7:16
What is the exact priority of the last 3 rules? 3-2-4 or 3-4-2 or 4-3-2? — Peter Pesch
– Peter Pesch, Commented Dec 12, 2017 at 7:21

Jerry · Accepted Answer · 2017-12-12 07:45:52Z

Things like this are usually easier if you tackle the problem in order of priority. It looks like 1 has the highest priority:

=IF(COUNTIF(Q2:U2,"X")>0,"X","Does not contain X")

COUNTIF(Q2:U2,"X") returns the number of occurrences of X in the range Q2:U2.

In place of "Does not contain X", we can first check for condition 2:

=IF(SUMPRODUCT(--ISNUMBER(Q2:U2))>0,"∆","Does not contain X or dates")

A date in excel is literally a number with some decorative formatting. I am using ISNUMBER to find the numbers and SUMPRODUCT to count the identified cells in the range. If there are more than 0 (at least 1), then it will become ∆.

In place of "Does not contain X or dates" now, we could check for Os. I would count the Os and add the cells with - (conditions 3 and 5 together) and see if they add up to the total cells in Q2:U2 (which is 5 in this case):

=IF(COUNTIF(Q2:U2,"O")+COUNTIF(Q2:U2,"-")=5,"O","FALSE")

When combined, it would become:

=IF(COUNTIF(Q2:U2,"X")>0,"X",IF(SUMPRODUCT(--ISNUMBER(Q2:U2))>0,"∆",COUNTIF(Q2:U2,"O")+COUNTIF(Q2:U2,"-")=5,"O","FALSE"))

Since all these involve counts, it might be easier setting up helper columns, something like:

   | Q | R | S | T | U |       V    |        W       |     X      |     Y      |     Z
1  |   |   |   |   |   | Count of X | Count of dates | Count of O | Count of - | Final value
2  |   |   |   |   |   |       A    |        B       |     C      |     D      |     E

A will be:

=COUNTIF(Q2:U2,"X")

B will be:

=SUMPRODUCT(--ISNUMBER(Q2:U2))

C will be:

=COUNTIF(Q2:U2,"O")

D will be:

=COUNTIF(Q2:U2,"-")

E will be:

=IF(V2>0,"X",IF(W2>0,"∆",X2+Y2=5,"O","FALSE"))

Collectives™ on Stack Overflow

How to create complex nested If statement in Excel?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related