Excel IF function

♦ The logical functions in Excel are a small group consisting of six functions
♦ These functions are noted for their black or- white results
♦ A logical function can return only one of two values: TRUE or FALSE
♦ The most common and powerful of the logical functions in Excel is the IF function
♦ This function is particularly powerful because it can test for a particular condition in the worksheet and use use one value is the on condition is TRUE and another is the condition is FALSE

♦ The format of the IF function is:
IF(logical_test, value_if_true, value_if_false)
♦ Logical_test is any value or expression that can be evaluated to TRUE or FALSE
♦ Value_if_true is the value that is returned if logical_test is TRUE
♦ Value_if_false is the value that is returned if logical_test is FALSE
♦ Value_if_true and/or Value_if_false can be another formula

♦ Consider the following IF function:
=IF(B2>=1000,100,50)

♦ If the Logical_test is TRUE, or in other words if the value of B2 is greater than or equal to 1000, than the function returns a value of 100
♦ If the Logical_test is FALSE, or in other words if the value of B2 is not greater than or equal to 1000, than the function returns a value of 50


♦ Consider the following IF function:
=IF(B2>=1000,B2*0.1,B2*0.05)

♦ If the Logical_test is TRUE, or in other words if the value of B2 is greater than or equal to 1000, than the function returns 10% of B2
♦ If the Logical_test is FALSE, or in other words if the value of B2 is not greater than or equal to 1000, than the function returns 5% of B2

♦ Consider the following IF function:
=IF(B2>=1000,”A $1000 or better”, “Less than a grand”)

♦ If the value of B2 is greater than or equal to 1000, than the function returns the string “A $1000 or better”
♦ If the value of B2 is not greater than or equal to 1000, than the function returns the string “Less than a grand”
♦ When you use text as the value_if_true or the value_if_false arguments, you must enclose the text in a pair of double quotation marks (“ “)

Practice:

A firm pays its salespersons by a commission system. Each unit costs $30 and each salesperson is paid $5 per unit sold. As an added incentive, a bonus of 7% of the total sales made should the salesperson’s sales exceed $1000 is awarded. Calculate the wages earned by each of the salespersons:

Salesperson Units Sold
Jennifer Smith 34
Adrian Walshe 27
Norah Walker 38
Deirdre Connolly 29
Michael Durnin 44