Excel IF statement

IF statement is one of the most common programming function. It is also one of the most used ones in Excel. The IF function can be used with one condition, as described below or with multiple conditions evaluated using the AND / OR functions

How does the IF function work

The IF function works like this. There are two branches. An IF branch and an ELSE branch. As an example you can use this to assign a value if something happens or not.

If event happens then assign a value else assign another value.

For example: IF Traffic Light = “Green” then “Go” else “Stop”

Let’s write this in Excel “=IF(A2=”Green”,”Go”,”Stop”)”

Here is how the formula works

In the first part of the formula, you put a condition, a question. The condition is for the value of A2 to be the word “Green”. If this is TRUE, then the B2 will get the first value. If this is FALSE, then B2 gets the second value. To prove this, let’s put a different value than “Green” in the A2 cell

Examples of the usage of IF function

The IF function can be used to find duplicates in a list, if the list is sorted by the column that contains the duplicates. Find a duplicate finding example here.

Here is a list of top movies with the year of production and total revenue. the IF statement can help ask the following questions highlighted in yellow.

Here are the formulas and the results

Nested IF’s

One important variation of the IF formula is that you can use nesting to add another IF formula or other formulas instead of the value. For ex, in the example above if we want to find out if the movie is made after 2000 and made over 1 billion dollars we can use the formula like this.

Notice how the second IF is “nested” in the TRUE branch of the first IF. In this case, the first IF is evaluated (was the movie made after year 2000?). If the answer is TRUE, thene the second IF is evaluated (did the movie make over 1 billion$?). The resulting value is Yes only if both conditions are TRUE. It is “No” if the first condition is TRUE and the second is FALSE. It is also “No” if both conditions are FALSE.

Here’s how this works.