The IF Function

You won't get very far in Excel before you need to use an IF function. The IF function allows you to pose a question with two possible solutions. In other words, it is IF some condition is true, THEN do something, ELSE do something different. It's as simple as that. But it's also incredibly powerful. Let's look at some examples. 

The basic syntax is: =IF(logical_test, value_if_true, value_if_false)

	A	B	C	D	E
1	Account	Budget	Actual	Variance	Formula in Cells D2,D3,D4, and D5
2	Travel	$1,400.00	$1254.54	Not Over Budget	=IF(C2>B2,"Over Budget","Not Over Budget")
3	Internet	$100.00	$94.54	Not Over Budget	=IF(C3>B3,"Over Budget","Not Over Budget")
4	Shipping	$50.00	$75.14	Over Budget	=IF(C4>B4,"Over Budget","Not Over Budget")
5	Supplies	$250.00	$265.69	Over Budget	=IF(C5>B5,"Over Budget","Not Over Budget")

The simple ledger above shows one use of the IF function. In this case, we are determining whether or not the 'Actual' expenses exceed the values in the 'Budget' column. 

NESTED IF STATEMENTS

The analogy I think of when describing the standard IF statement is driving down a highway with a fork in the road. The standard IF statement only has two ways to go...it's either True or False...there is no third road. When we need a third choice, you need to use more than one IF statement.

The table below shows four students in a classroom with the results of their most recent test. We want to convert their score to a letter grade. With five possible letter grades, a single IF statement would not be sufficient.

	A	B	C	D
1	Student	Test Score	Grade	Formula in C2,C3,C4 and C5
2	Adam	83	B	=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))
3	Jose	65	D	=IF(B3>=90,"A",IF(B3>=80,"B",IF(B3>=70,"C",IF(B3>=60,"D","F"))))
4	Danielle	90	A	=IF(B4>=90,"A",IF(B4>=80,"B",IF(B4>=70,"C",IF(B4>=60,"D","F"))))
5	Eloise	76	C	=IF(B5>=90,"A",IF(B5>=80,"B",IF(B5>=70,"C",IF(B5>=60,"D","F"))))

The formula in Column C determines the letter grade of each student based on their Test Score in Column B. It is a nested IF formula, with exactly 4 IF statements. Here's how it reads:

• IF the student's test score is greater than or equal to 90, then give an "A"
  • Else, if it is greater than or equal to 80, then give a "B"
    • Else, if greater than or equal to 70, then give "C"
      • Else, if greater than or equal to 60, give "D"
        • Else it must be <60, so give an "F"

The hardest thing with nested IF statements is keeping track of the many parentheses, because they can get a little out of hand. I use a little trick to keep them in line:

1. First, write your initial IF statement (the "temp" will make sense in a moment): =IF(B2>=90,"A","temp")
2. Copy the entire formula (except for the equal sign)
3. Highlight "temp" in the original function and paste the copied text over it. You should now have this: =IF(B2>=90,"A",IF(B2>=90,"A","temp"))
4. You now have two IF statements with parentheses in all the right places. Edit your second IF statement to reflect your second condition.
5. Keep repeating until you have all of the IF statements you need
6. Replace the only remaining "temp" value with the last option

MULTI-CONDITIONAL IF STATEMENTS

Nested IF statements are for when you need more than two possible outcomes (like the five letter grades for our students' test scores). But what if you have more than one condition that needs to be met. 

	A	B	C	D	E
1	Student	Math Grade	Reading Grade	Final Grade	Formula in Cells D2,D3,D4, and D5
2	Adam	B	B	PASS	=IF(AND(B2<>"F",C2<>"F"),"PASS","FAIL")
3	Jose	D	B	PASS	=IF(AND(B3<>"F",C3<>"F"),"PASS","FAIL")
4	Danielle	A	A	PASS	=IF(AND(B4<>"F",C4<>"F"),"PASS","FAIL")
5	Eloise	C	F	FAIL	=IF(AND(B5<>"F",C5<>"F"),"PASS","FAIL")

It is the magic of the AND statement that is allowing you to check for two conditions. In this case, we are making sure that neither of the student's scores (Math Grade and Reading Grade) were an F. We could have just as easily and accurately said the following:

=IF(OR(B2="F",C2="F"),"FAIL","PASS")

This uses an OR statement instead of an AND statement. Rather than passing the student if neither grade matches an F, it fails a student if at least one of the grades is an F...it's the same thing but attacked from a different angle.