IFs and Nested IFs in Excel
Objectives:
•Using the If function in spreadsheets
•Nesting If functions
CS&E 1111 ExIFs
The IF function
The IF function is a logical function used for
making decisions based on some condition(s)
Conditions have to result in Boolean values.
(relational expressions, true/false)
Lends more flexibility since the results can be
something else besides a simple True - False
value
If your total points are less than 600 then display
“Fail” in the cell otherwise display “Pass”
CS&E 1111 ExIFs
The IF function syntax
=IF(logical_test, value_if_true,value_if_false)
The IF function has 3 arguments - the first two are required
First argument <logical_test> : is any value or expression
that can be evaluated to TRUE or FALSE.<condition>
Second argument: the value that is returned if logical_test
is TRUE (then)
Third argument: the value that is returned if logical_test is
False (else) – this argument is optional
Sometimes you may see the statement described as
If(<condition>, if true then, else)
CS&E 1111 ExIFs
Examples – IF’ expressions as conditions
Write a formula in cell G4 to determine if SHALINI
passed or failed (a minimum of 150 points is required
to pass)
=IF(F5>=150, “pass”, “fail”) or IF(F5<150, “fail”, “pass”)
COMPOUND CONDITION
=IF(OR(C5<40,D4<40,E4<40),”FAIL”,”PASS”)
CS&E 1111 ExIFs
Conditions
Simple condition
mark < 34
mark >=35
grade = “A”
Compound condition using AND, OR
AND(AGE>25, CODE=1)
OR(MARK1<40,MARK2<40)
CS&E 1111 ExIFs
How can you write a formula that requires
a series of conditions to be evaluated each
dependent upon the results of the previous
condition?
If Shalini has at least 210 points he
will get an A , else if he has less than
210 points but at least 180 points he
will get a B, otherwise he gets a “C”
CS&E 1111 ExIFs
Before designing a nested-if formula – Use a logic
diagram (Decision Tree) to understand the logic.
gets an A
True
Marks gets B
>=210
False
Marks True
have at
least 180
points?
1st If
False
statemen gets a C
t 2nd If
statemen
t
CS&E 1111 ExIFs
NESTED IF
The second argument and/or third argument of an IF
statement can be another IF statement - up to 64
levels of nesting! Here is an example with 2 levels of
nesting – so three outcomes are possible.
IF(condition1,true_value1,IF(condition2,true_
value2,false_value))
CS&E 1111 ExIFs
Nested IF Example
Write a formula in cell F4:
If Blue has at least 350
points he gets an “A” , if he
has less than 350 points but
at least 300 points he gets a
“B”, otherwise he gets a “C”
=If(f4>=210, “A”, IF(f4>=180, “B”, “C”))
1st logical test Value-if-True Value-if-False is a nested If
CS&E 1111 ExIFs
What to remember about using IF’s
An IF function allows you to make a decision based
on a criteria. The first argument contains the
logical_test, the 2nd the value-if-true and the 3rd
(optional) the value-if-false.
For any IF statement, either the second argument
(TRUE) or third argument (FALSE) is applied, not
both.
Consider if the criteria are not mutually exclusive this
will affect the order of the arguments.
In a Nested IF statement, each IF must be complete
with all required arguments specified and a
corresponding set of ( ).
CS&E 1111 ExIFs
