Writing an IF function is quite straight forward, if there is only one logical test involved.

For example, we want to comment on a shop’s performance by looking at its actual sales. If Actual hits Budget, then Good job, else Sell More.  In Excel, it will be translated as follow:

How about we need to consider one more thing, say if there is any complaint to the shop?

• IF Actual hits Budget AND no complaint ==> Good job
• IF Actual hits Budget AND there is complaint ==> Serve better
• IF Actual does not hits Budget AND no complaint ==> Sell More
• IF Actual does not hits Budget AND there is complaint ==> In trouble

In this case, a nested IF formula is required:

It looks complicated… but it is not if you follow a flowchart and build the formula step by step.

First of all, let’s take a look at the syntax:

IF(logical_test,[value_if_true],[value_if_false])

• The first argument –  a logical test that should always return an either True or False
• The second argument (optional) – what you want if the logical test returns True; if omits, returns TRUE
• The third argument (optional) what you want if the logical test returns False; if omits, returns FALSE

Let’s examine our first example in a flowchart, where we will look at only one scenario – Sales.

The following simple IF formula would do the job.

=IF(B2>=B1,"Good job","Sell More")

Now let’s look at the second example, where another scenario to be considered – #Complaint.

• IF Actual hits Budget AND no complaint ==> Good job
• IF Actual hits Budget AND there is complaint ==> Serve better
• IF Actual does not hits Budget AND no complaint ==> Sell More
• IF Actual does not hits Budget AND there is complaint ==> In trouble

The above statements can be translated into the flowchart below:

In Excel, the formula will become:

=IF(B2>=B1,IF(B3=0,"Good job","Serve better"),IF(B3=0,"Sell more","In trouble"))

where B1 is Budget, B2 is Actual, B3 is #Complaint

Stay calm!  We can write this formula by following the flow of the chart:

=IF(B2>=B1,

=IF(B2>=B1,IF(B3=0,

=IF(B2>=B1,IF(B3=0,“Good job”,

=IF(B2>=B1,IF(B3=0,”Good job”,“Serve better”)

Note: this is the end of the second argument [value_if_true] which happened to be the nested IF, thus a close parenthesis is needed.

=IF(B2>=B1,IF(B3=0,”Good job”,”Serve better”),IF(B3=0,

=IF(B2>=B1,IF(B3=0,”Good job”,”Serve better”),IF(B3=0,“Sell more”,

=IF(B2>=B1,IF(B3=0,”Good job”,”Serve better”),IF(B3=0,”Sell more”,“In trouble”)

Note: this is the end of the 3rd argument [value_if_false] which happened to be the nested IF, thus a close parenthesis is needed.

Here come the final formula:

=IF(B2>=B1,IF(B3=0,"Good job","Serve better"),IF(B3=0,"Sell more","In trouble"))

Note: There is another parenthesis in the end, which concludes the IF statement.

With your problem presented in a flowchart, writing nested IF formula should no longer be a headache to you.  Try to add extra scenarios to the flowchart and follow through the steps above, you should be able to finish your nested IF formula in one go (with some practices).

Tip: Pay attention to the function screentips during writing.    It gives you hints on which argument of a function you are dealing with.

Theoretically, you have may 64 nested IF (7 nested IF before Excel 2007).  Practically, you won’t need so many.  In many cases when you are stuck with too many nested IF, your problem is probably a LOOKUP problem instead.  Let’s talk about it in the next post.

Stay tuned.