The IF Function in Excel
Purpose: the IF function automatically enters data (i.e. a word or a number) into a cell
depending on a content of another cell.
Example:
Below is a spreadsheet of students and their marks. In column C, the IF function
is used to automatically enter the words PASS or FAIL, depending on the student’s
mark.
There are 3 parts to the IF function: the test condition, a True result and a False result.
=IF(B3>=50,"PASS","FAIL")
Test True False
condition result result
The way to read the IF function is to say: if B3 is greater than or equal to 50, then enter
PASS, otherwise enter FAIL.
Logic Symbols in Excel:
= equals
< less than
> greater than
<= less than or equal to
>= greater than or equal to
Examples of IF statements:
1. =IF(B8>100, 50, -10)
outputs a number (either 50 or -10) depending on whether the number in cell B8 is
greater than 100
2. =IF(C11=10.25, “Minimum Wage”, ”Above Minimum Wage “)
Outputs one of the phrases. Note that text must appear within quotation marks in the IF
function.
3. =IF(C7=10.25, “Minimum Wage”, ” “)
Outputs “Minimum Wage” if cell C7 contains the number 10.25. Otherwise no words
are printed. If the “ “ blank space is left out of the IF statement, the word FALSE is
entered automatically.
4. =IF(D4=”staff”, 11.55 , 0)
If D4 contains the word “staff” (spelled exactly that way), then the output is 11.55.
Otherwise, a zero is entered. Note that words must be within quotation marks.
5. =IF(F20>40, C20*40+(B20-40)*C20*1.5, B20*C20)
If F20>40, then it calculates overtime pay. Otherwise, it calculates regular pay using a
different formula.
6. =IF(G3=”bonus”, B24*2, 0)
If G3 says “bonus”, then it calculates the amount of bonus. Otherwise, a zero is entered
for the amount of bonus.
YOUR TURN!
TASK: Create your own IF function. You may work in pairs if you wish. Submit your
table in the drop box: IF function.
