IF, it is too complicated…

Excel tips -IF(0)

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:

Excel tips - IF

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:Excel tips - IF(2)

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.

Excel tips - IF(1)

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:

Excel tips - IF(3)

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,Excel tips - IF(4)

=IF(B2>=B1,IF(B3=0,Excel tips - IF(5)

=IF(B2>=B1,IF(B3=0,“Good job”,Excel tips - IF(6)

=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.Excel tips - IF(7)

=IF(B2>=B1,IF(B3=0,”Good job”,”Serve better”),IF(B3=0,Excel tips - IF(8)

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

=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.Excel tips - IF(10)

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.  Excel tips - IF(0)  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.

Advertisements
This entry was posted in Formula and tagged . Bookmark the permalink.

12 Responses to IF, it is too complicated…

  1. Pingback: Nesting with IF, AND and OR functions

  2. Oz du Soleil says:

    VERY VERY nice!

    Like

  3. Steve Myles says:

    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

    Like

  4. Igor says:

    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!

    Like

  5. XLarium says:

    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.

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s