CoverAcknowledgementsExcel OverviewTime SaverAuto Fill and Fill SeriesCell ReferencingAbsolute and Relative ReferencingNavigation ShortcutsManaging CellsSorting and FilteringHiding and GroupingPaste SpecialNamed RangesRankingRoundingSum and CountManaging WorksheetsLookup Functions Data Validation IF Statements Formula Errors Text FunctionsText to ColumnsLogic Functions 2Date and TimeConditional Formatting 1Standards of Professionalism Spreadsheet Formatting and Design Basic ChartsDashboardsConditional Formatting 2Spreadsheet Protection Basic Pivot TablesAdvanced Pivot Tables and Slicers Logic Functions 3Advanced Lookup FunctionsLoan Amortization Schedule Advanced ChartsMacros 1Macros 2Macros 3How to Use Search Engines

Logic Functions 2

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.

Practice Spreadsheet

Use this workbook for the chapter.

BUS115_image_33.1.png
Figure 33.1

IF Function

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.

  1. Type =IF and a left parenthesis ( ( ) to start the function on the first employee's row.
  2. Select the cell containing the employee's tenure data.
  3. Type >15 to indicate a greater than 15 evaluation and type a comma ( , ).
  4. Type 5000 as the bonus value for the employee if the greater than evaluation is true.
  5. Type a comma ( , ).
  6. Type 1000 as the bonus value for the employee if the greater than evaluation is false.
  7. Type a right parenthesis ( ) ) to close the formula.
  8. Press Enter to complete the function (shown as =IF(E3>15,5000,1000) in Figure 33.2).
BUS115_image_33.2.png
Figure 33.2

OR 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.

  1. Type =IF and a left parenthesis ( ( ) to start the function on the first employee's row.
  2. Type OR and a second left parenthesis ( ( ).
  3. Select the cell containing the employee's tenure data.
  4. Type >15 to indicate a greater than 15 tenure evaluation and type a comma ( , ).
  5. Select the cell containing the employee's GPA data.
  6. Type >3.5 to indicate a greater than 3.5 GPA evaluation.
  7. Type a right parenthesis ( ) ) to close the OR formula.
  8. Type a comma ( , ).
  9. Type 5000 as the bonus value for the employee if the greater than evaluation is true.
  10. Type a comma ( , ).
  11. Type 1000 as the bonus value for the employee if the greater than evaluation is false.
  12. Type a right parenthesis ( ) ) to close the IF formula.
  13. Press Enter to complete the function (shown as =IF(OR(E3>15,F3>3.5),5000,1000) in Figure 33.3).
BUS115_image_33.3.png
Figure 33.3

AND Function

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.

  1. Type =IF and a left parenthesis ( ( ) to start the function on the first employee's row.
  2. Type AND and a second left parenthesis ( ( ).
  3. Select the cell containing the employee's tenure data.
  4. Type >15 to indicate a greater than 15 tenure evaluation and type a comma ( , ).
  5. Select the cell containing the employee's GPA data.
  6. Type >3.5 to indicate a greater than 3.5 GPA evaluation.
  7. Type a right parenthesis ( ) ) to close the AND formula.
  8. Type a comma ( , ).
  9. Type 5000 as the bonus value for the employee if the greater than evaluation is true.
  10. Type a comma ( , ).
  11. Type 1000 as the bonus value for the employee if the greater than evaluation is false.
  12. Type a right parenthesis ( ) ) to close the IF formula.
  13. Press Enter to complete the function (shown as =IF(AND(E3>15,F3>3.5),5000,1000) in Figure 33.4).
BUS115_image_33.4.png
Figure 33.4

Nested IF Function

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.

BUS115_image_33.5.png
Figure 33.5
 

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.

  1. Type =IF and a left parenthesis ( ( ) to start the function on the first employee's row.
  2. Select the cell containing the employee's tenure data.
  3. Type > to indicate a greater than evaluation.
  4. Select and anchor the table's cell containing 30 to indicate a greater than 30 evaluation and type a comma ( , ). (See Figure 33.5)
  5. Select and anchor the cell containing the bonus value for 30 years ($10,000) and type a comma ( , ).
  6. Type IF and a left parenthesis ( ( ) to insert a new IF argument if the greater than evaluation is false.
  7. Repeat steps 2–6 for each value of Years and Bonus appropriately until the final value_if_false argument.
  8. Type 0 (zero) as the final value_if_false argument.
  9. Type six right parentheses ( ) ) to close all IF formulas.
  10. Press Enter to complete the function (=IF(E8>$P$8,$Q$8,IF(E3>$P$7,$Q$7,IF(E8>$P$6,$Q$6,IF(E8>$P$5,$Q$5,IF(E8,$P$4,$Q$4,IF(E3>$P$3,$Q$3,0)))))) as the final formula).

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.

Match and Index Functions

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.

  1. Type =MATCH and a left parenthesis ( ( ) to start the function on the first employee's row.
  2. Select the cell containing the employee's tenure data and type a comma ( , ).
  3. Select and anchor the table's range of cells containing year values and type a comma ( , ).
  4. Type 1 to indicate an approximate less than match.
  5. Type a right parenthesis ( ) ) to close the formula.
  6. Press Enter to complete the function (shown as =MATCH(E3,$P$3:$P$9,1) in Figure 33.6).
BUS115_image_33.6.png
Figure 33.6

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.

BUS115_image_33.7.png
Figure 33.7
 

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.

BUS115_image_33.8_1.png
Figure 33.8
 

Supplemental Resource

End-of-Chapter Survey

: How would you rate the overall quality of this chapter?
  1. Very Low Quality
  2. Low Quality
  3. Moderate Quality
  4. High Quality
  5. Very High Quality
Comments will be automatically submitted when you navigate away from the page.
Like this? Endorse it!