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(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.

Pingback: Nesting with IF, AND and OR functions

VERY VERY nice!

LikeLike

Thanks for your kind words!

Cheers,

LikeLiked by 1 person

I really like tutorials that dare step away from Excel and help explain structure, thoughts processes and other things that aren’t immediately visible. Your flow charts help get the lesson across.

BRAVO!

LikeLiked by 1 person

http://www.excelhero.com/blog/2010/01/i-heart-if.html

This allows one to avoid long complicated if statements and is more efficient and easier to read

LikeLike

Hi Steve,

That is a great article. Thanks for sharing!

Cheers,

LikeLiked by 1 person

for some reason this CHOOSE formula does not work in my spreadsheet, it selects 2 after ((B2>=B1) and if I remove 2 it gives me #VALUE!

LikeLike

Hi Igor,

You need to put the operator ” * ” for (B2>=B1)… i.e.

=CHOOSE((B2>=B1) * 2+(B3=0)*1+1,”In trouble”,”Sell more”,”Serve better”,”Good job”)

LikeLike

Sorry, Igor. Either I forgot the multiplication signs or the blog software removed it.

LikeLike

I thought it is the software that removed it

It happened to me

LikeLike

With more than one criteria I try to build an index and use CHOOSE(). In this example:

=CHOOSE((B2>=B1)

2+(B3=0)1+1,”In trouble”,”Sell more”,”Serve better”,”Good job”)The formula is usually shorter and doesn’t contain multiple references to the same cell.

LikeLike

This is a nice one.

Thanks for sharing, XLarium 😀

LikeLike