Excel logic functions are powerful tools to evaluate data. In addition to the IF logic function, the OR and AND functions can expand the logic criteria when used in conjunction with the IF function.
For this chapter, we will reference data from Figure 33.1, including employee names, genders, positions, tenures, GPAs, and salaries. We want to evaluate the data in varying scenarios to award employees with a bonus.
Use this workbook for the chapter.
In the first scenario, we want to give employees who have a tenure greater than 15 years $5,000, and $1,000 for those with a tenure of 15 and less. We can evaluate the criteria using the IF function.
If we want to evaluate multiple criteria and award the employee based on tenure or GPA, we can use the OR function within an IF function. In this example, we will use an OR function to check if the employee's tenure is greater than 15, or if their GPA is greater than 3.5, and award them with $5,000 if one of the conditions is true. Otherwise, the employee will receive $1,000 if neither tenure nor GPA is greater than the set value.
The AND function requires both criteria to be true. We will use this function to evaluate if the employee's tenure is greater than 15 and GPA is greater than 3.5 to determine if the employee receives a $5,000 bonus.
A nested IF function sets a condition within another IF function's true or false arguments. These may be chained multiple times to set degrees of logic. For example, we want to build a varying bonus amount based on the number of years an employee has been with the company. If the employee has a tenure of 11–15 years, they will receive $2,000. Another employee with a tenure of 21–25 years will receive $6,000.
First, we'll build a table to determine every tenure range and bonus amount (see Figure 33.5). The first column will contain years beginning with five and increase in integrals of five until 30 years. The second column will contain bonus amounts beginning with $1,000 for five years and increase to $2,000, $5,000, $6,000, $7,500, and $10,000. We'll add a final row for 100 years with $10,000 for reference.
Then we'll write an IF function using the table. It will start by awarding $10,000 to employees with a tenure greater than 30 years, but if the logic is false (the employee's tenure is 30 or below), a new IF function will be inserted for the false argument and descend through the table's data. The chain of IF functions for false conditions will continue until the table's final values wherein the false argument returns zero for tenure below five.
However, a nested IF function can be complex and prone to errors. There is a time and place for nested IF functions, but there may be more efficient alternatives.
The Match and Index functions can achieve the same results as a nested IF function. We'll start with the Match function to identify the row in the Figure 33.5 chart that closely matches the employee's tenure.
Now we need to add the Index function to return the value associated with the tenure match. To do it, we'll insert the Index function before Match and select the range of cells containing the bonus data for the lookup_value argument. The Match function will take the place of the Index function's lookup_array. Finally, the match type will be 1 to indicate an approximate less than match.
The results may be a little different from the nested IF function due to a difference in the evaluations. The nested IF function returned bonus amounts based on if the employee's tenure is greater than the table's Year values. Whereas the Index and Match functions return a bonus amount based on if the employee's tenure is less than or equal to the chart's values.
These functions will also result in an error if the employee's tenure is less than five because the chart does not include a row for years below five. One way to resolve this problem is to enclose the Index and Match operations with another function: IFERROR. The IFERROR function can be set to return zero if the other functions produce an error.
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/logic_functions_2.