Background Information
The IF function is one of the most versatile and frequently-used functions in Excel since it provides the basis for Excel to make a decision. Any employee wishing to master Excel should be comfortable writing IF functions by understanding the syntax and general guidelines as outlined in this Job Aid.
Function Syntax
In order for Excel to recognize an IF function, it must be written in the following manner:
=IF(Comparison, Value if True, Value if False)
|
Comparison (logical_test)
|
Value if Comparison is True
|
Value if Comparison is False
|
|
Any value or expression that can be evaluated as TRUE or FALSE
|
The value that is returned if the logical_test evaluates to TRUE
|
The value that is returned if the logical_test evaluates to FALSE
|
|
This is the comparison you are conducting.
All comparisons include a comparison operator (=, >, <, >=, <=, <>)
|
If the logical test is true, then this value will be displayed in the cell that the function is written
|
Otherwise, this value will be displayed in the cell that the function is written
|
Example
The worksheet in the attached pdf demonstrates an example of the IF function. The function is written in cell D3 and is designed to look at the value in column C. If the value is greater than $100,000 (C3>100000), then the word Yes will be written in column D. If the value is less than or equal to 100,000, then the word No will be written in column D.
Sample Exercises
Using the spreadsheet in the attached pdf, fill in the blanks to create a formula that answers the questions in the table.
|
Question
|
Logical_test
|
Value_if_true
|
Value_if_False
|
Function
|
|
STATUS? - If the employee averages 64 hours or more per pay period, then write Full Time in cell G4, otherwise, write Part Time.
|
F4>=64
|
“Full Time”
|
“Part Time”
|
=IF(F4>=64,"Full Time","Part Time")
|
|
HEALTH INSURANCE? - IF the employee is Full Time status, then type Yes in cell H4, otherwise, leave H4 empty.
|
G4="Full Time"
|
“Yes”
|
“”
|
=IF(G4="Full Time","Yes","")
|
|
TUITION? - If the employee averages 64 hours or more per pay period and has been employed for 180 days or more, then write Yes in I4, otherwise, leave I4 blank.
|
AND(TODAY()-C4>180,F4>=64)
|
“Yes”
|
“”
|
=IF(AND(TODAY()-C4>180,F4>=64),"Yes","")
|
To review images or save/download/print a copy of this Job Aide, please select the pdf attachment.