IF with AND and OR

Now, we'll extend the functionality of the IF function by nesting AND and OR functions.

We are looking for students who have all "A's" on their tests using IF with a nested AND function.

The formula will test to see if all of the conditions are true.

If so, the result of the IF function is TRUE.

For all "A's", we need to know if a student's score on Test 1 is greater than or equal to 90 (the cursor is next to the cell reference; I am pressing F4 to make it an absolute cell reference), and their score on Test 2 is greater than or equal to 90 (I am pressing F4 again), and their score on Test 3 is greater than or equal to 90.

If they are all greater than or equal to 90 the IF function returns Yes; if not, it returns No.

I am copying the formula.

We can see that only Mo has all "A's".

Let's take a look at how that worked.

AND returns TRUE when all arguments evaluate to TRUE, and FALSE when at least one argument evaluates to FALSE.

For the IF function, when logical_test evaluates to TRUE, the IF function returns value_if_true.

When logical_test evaluates to FALSE, the function returns value_if_false.

AND is nested in the IF function and is the logical_test.

When all arguments in the AND function evaluate to TRUE, IF returns the value_if_true.

When at least one argument in the AND function evaluates to FALSE, IF returns the value_if_false.

Now, we are looking for students who have at least one "A" in their tests, using IF with a nested OR function.

For at least one "A", we need to know if a student's score on Test 1 is greater than or equal to 90, or their score on Test 2 is greater than or equal to 90, or their score on Test 3 is greater than or equal to 90.

If at least one test is greater than or equal to 90, the IF function returns Yes.

If none of the tests are greater than or equal to 90, it returns No.

Both Bob and Mo have at least one "A".

Let's look at how that worked. It's similar to the nested AND function.

OR returns TRUE if any argument is TRUE and FALSE when all arguments are FALSE.

In this example, OR is nested in the IF function and is the logical_test.

If any arguments in the OR function evaluates to TRUE, IF returns the value_if_true.

If all arguments in the OR function evaluate to FALSE, IF returns the value_if_false.

Up next, the COUNTIFS and SUMIFS functions.