Excel - IF with AND and OR Tutorial

In this video, you will learn about the intelligent search feature in Microsoft 365. The video covers the search tools in Microsoft 365, including searching on your computer, using Microsoft Search, finding content and people in SharePoint, searching your emails, backing up and saving notes, and more.
This will help you save time and find everything you need, whether it's a document, image, video, person, or conversation.
Gain efficiency in your searches with Microsoft's intelligent search tool in Microsoft 365.

3:35
4077 views

Available in Microsoft365

Objectifs :

This video aims to enhance the understanding of the IF function in Excel by demonstrating how to nest AND and OR functions. It focuses on evaluating student test scores to determine academic performance based on specific criteria.

Chapitres :

Introduction to Nested Functions
In this section, we will explore how to extend the functionality of the IF function by nesting AND and OR functions. This approach allows us to evaluate multiple conditions simultaneously.
Using IF with Nested AND Function
We are looking for students who have achieved all A's on their tests by using the IF function combined with a nested AND function. The formula checks if all specified conditions are true. For instance, we need to verify if a student's score on Test 1 is 90, Test 2 is 90, and Test 3 is 90. If all conditions are met, the IF function returns 'YES'; otherwise, it returns 'NO'. In our example, only Mo has all A's.
Understanding the AND Function
The AND function returns TRUE when all arguments evaluate to true and FALSE when at least one argument evaluates to false. In the context of the IF function, when the logical test evaluates to true, the IF function returns a value of TRUE. Conversely, if the logical test evaluates to false, it returns a value of FALSE. Thus, when all arguments in the AND function are true, the IF function confirms this by returning TRUE.
Using IF with Nested OR Function
Next, we will look for students who have at least one A on their tests using the IF function with a nested OR function. Here, we check if a student's score on Test 1, Test 2, or Test 3 is 90. If at least one test score is 90, the IF function returns 'YES'; if none are 90, it returns 'NO'. In this case, both Bob and Mo have at least one A.
Understanding the OR Function
The OR function operates similarly to the AND function but with a different logic. It returns TRUE if any argument is true and FALSE when all arguments are false. When OR is nested within the IF function, if any argument evaluates to true, the IF function returns TRUE. If all arguments evaluate to false, it returns FALSE.
Conclusion and Next Steps
In summary, we have learned how to use nested AND and OR functions within the IF function to evaluate student test scores. This method allows for more complex logical tests in Excel. Up next, we will explore the COUNTIFS and SUMIFS functions, which further enhance data analysis capabilities.

FAQ :

What is the purpose of the IF function in Excel?

The IF function is used to perform logical tests and return different values based on whether the test evaluates to TRUE or FALSE.

How does the AND function work in Excel?

The AND function checks multiple conditions and returns TRUE only if all conditions are met; otherwise, it returns FALSE.

What does the OR function do in Excel?

The OR function evaluates multiple conditions and returns TRUE if at least one condition is true; it returns FALSE only if all conditions are false.

How can I create an absolute cell reference in Excel?

To create an absolute cell reference, you can press F4 after selecting the cell reference in your formula, which will add dollar signs to lock the reference.

What is the difference between COUNTIFS and SUMIFS functions?

COUNTIFS counts the number of cells that meet specified criteria, while SUMIFS sums the values in a range that meet specified criteria.

Can I nest AND and OR functions within an IF function?

Yes, you can nest both AND and OR functions within an IF function to create complex logical tests.

Quelques cas d'usages :

Student Performance Evaluation

Using the IF function with nested AND to evaluate student scores, educators can determine if students have achieved all A's in their tests, which can help in identifying high achievers for scholarships or advanced programs.

Conditional Formatting for Grades

By applying the IF function with nested OR, teachers can highlight students who have at least one A in their tests, allowing for quick identification of students who may need additional support or recognition.

Data Analysis in Sales

Sales analysts can use COUNTIFS to count the number of sales that meet multiple criteria, such as sales made in a specific region and above a certain value, helping to identify successful sales strategies.

Budget Management

Using SUMIFS, financial managers can sum expenses that meet specific criteria, such as all expenses over a certain amount in a particular department, aiding in budget tracking and management.

Glossaire :

IF function

A logical function in Excel that returns one value if a condition is true and another value if it is false.

AND function

A logical function that returns TRUE if all its arguments are true and FALSE if at least one argument is false.

OR function

A logical function that returns TRUE if at least one of its arguments is true and FALSE if all arguments are false.

Absolute cell reference

A cell reference that remains constant, even when the formula is copied to another cell, typically denoted by a dollar sign (e.g., $A$1).

Logical test

An expression that evaluates to TRUE or FALSE, used in functions like IF to determine which value to return.

COUNTIFS function

A function that counts the number of cells that meet multiple criteria across multiple ranges.

SUMIFS function

A function that sums the values in a range that meet multiple criteria.

00:00:06

by nesting AND and OR functions.

00:00:10

We're looking for students who have all As on their tests,

00:00:12

using IF with a nested AND function.

00:00:16

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

00:00:20

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

00:00:27

For always, we need to know if a student's score on Test 1 is >= 90.

00:00:36

The cursor is next to the cell reference.

00:00:39

I'm pressing F4 to make it an absolute cell reference.

00:00:44

And their score on Test 2 is >= 90,

00:00:48

I'm pressing F4 again, and their score on Test 3 is >=90.

00:00:56

If they're all >=90, the IF function returns YES;

00:01:01

if not, it returns NO.

00:01:08

I'm copying the formula.

00:01:14

We can see that only Mo has ''All A's''.

00:01:18

Let's take a look at how that worked:

00:01:21

AND returns TRUE when all arguments evaluate to true,

00:01:26

and FALSE when at least one argument evaluates to false.

00:01:31

For the IF function, when the logical test evaluates to true,

00:01:35

the IF function returns value of TRUE;

00:01:40

when logical test evaluates to false, the function returns a value of FALSE.

00:01:47

AND is nested in the IF function, and is the logical test.

00:01:52

When all arguments in the AND function evaluate to true,

00:01:55

IF returns the value of TRUE;

00:01:58

When at least one argument in the AND function

00:02:01

evaluates to false, IF returns the value of FALSE.

00:02:08

Now we're looking for students who have at least one A on

00:02:10

their tests, using IF with a nested OR function.

00:02:17

For at least one A, we need to know if a student's score

00:02:20

on Test 1 is >= 90,

00:02:26

or their score on Test 2 is >= 90,

00:02:31

or their score on Test 3 is >= 90.

00:02:37

If at least one test is >= 90,

00:02:41

the IF function returns YES.

00:02:43

If none of the tests are >= 90,

00:02:46

it returns NO.

00:02:52

Both Bob and Mo have at least one A.

00:02:55

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

00:03:00

OR returns TRUE if any argument is true,

00:03:04

and FALSE when all arguments are false.

00:03:08

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

00:03:14

If any argument in the OR function evaluates to true,

00:03:18

IF returns a value of TRUE.

00:03:21

If all arguments in the function evaluate to false,

00:03:25

IF returns a value of FALSE.

00:03:30

Up next: the COUNTIFS and SUMIFS functions.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2675 Views

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Po...

ALL COURSES