Nesting the AND, OR and IF Functions in Excel

of 01

Combining the OR and IF, or the AND and IF Functions in Excel

Nesting the AND, OR and IF Functions in Excel
Nesting the AND, OR and IF Functions in Excel. © Ted French

AND, OR and IF Functions Overview

The AND, OR and IF functions are some of Excel's better known logical functions.

What the OR and  AND Function do, as shown in rows two and three in the image above is test multiple conditions and, depending on which function is used, one or all of the conditions must be true for the function to return a TRUE response. If not, the function returns FALSE as a value. 

In the image above, three conditions are tested by the formulas in rows two and three:

  • is the value in cell A2 less than 50;
  • is the value in cell A3 not equal 75;
  • is the value in cell A4 greater than or equal to 100.

For the OR function, if one of these conditions is true, the function returns a value of TRUE in cell B2

For the AND Function, all three conditions must be true for the function to returns a value of TRUE in cell B3.

Adding in the IF Function

When one of these two functions is combined with the IF function, the resulting formula has much greater capabilities.

Nesting functions in Excel refers to placing one function inside another. The nested function acts as one of the main function's arguments.

In the image above, rows four to seven contain formulas where the AND or OR function are nested inside the IF function.

In all of the examples, the nested function acts as the IF function's first or Logical_test argument.

=IF(OR(A2<50,A3<>75,A4>=100),"Data Correct","Data Error")


Changing the Formula Output

In all formulas in rows four to seven, the AND and OR functions are identical to their counterparts in rows two and three in that they test the data in cells A2 to A4 to see if it meets the required condition.

The IF function is used to control the formula's output based on what is entered for the function's second and third arguments.

This output can be:

  • text - row four;
  • a number - row five;
  • the output from a function or formula - row five (TODAY function) and row six;
  • a blank cell - row seven (cell B7).

In the case of the IF/AND formula in cell B5, since not all three cells in the range A2 to A4 are true - the value in cell A4 is not greater than or equal to 100 -  the AND function returns a FALSE value.

The IF function uses this value and returns its Value_if_false argument - the current date supplied by the TODAY function.

On the other hand, the IF/OR formula in row four returns the text statement Data Correct because

  1. the OR value has returned a TRUE value  - the value in cell A3 does not equal 75;
  2. the IF function then used this result to return its Value_if_false argument: Data Correct.

Writing the IF/OR Formula

The steps below cover how to enter the IF/OR formula located in cell B4 in the image above. The same steps can be used for entering any of the IF formulas in the example.

Although it is possible to just type the complete formula in by hand,

=IF(OR(A2<50,A3<>75,A4>=100),"Data Correct","Data Error")

many people find it easier to use the IF function's dialog box to enter the formula and arguments as the dialog box takes care of syntax such as comma separators between arguments and surrounding text entries in quotation marks.

The steps used to enter the IF/OR formula in cell B4 are:

  1. Click on cell B4 to make it the active cell;
  2. Click on the Formulas tab of the ribbon;
  3. Click on the Logical  icon to open the function drop down list;
  4. Click on IF in the list to open the IF function dialog box;
  5. Click on the Logical_test line in the dialog box;
  6. Enter the complete AND function: OR(A2<50,A3<>75,A4>=100) into the Logical_test line using pointing for the cell references if desired;
  7. Click on the Value_if_true line in the dialog box;
  8. Type in the text Data Correct - no quotation marks required;
  9. Click on the Value_if_false line in the dialog box;
  10. Type in the text Data Error;
  11. Click OK to close the dialog box and return to the worksheet;
  12. Since, as already discussed above,  the formula should display the  Value_if_true argument of Data Correct;
  13. When you click on cell B4, the complete function
    =IF(OR(A2<50,A3<>75,A4>=100),"Data Correct","Data Error") appears in the formula bar above the worksheet.