Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Excel AND Function
3.
Use of AND Function
4.
Excel OR Function
5.
Use of OR Function
6.
FAQs
7.
Key Takeaways
Last Updated: Mar 27, 2024
Easy

AND & OR Function in Excel

Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

People often don't know how to make AND and OR function in Microsoft Excel and get confused. Are you also the one? You are in the right place. 

This blog will not directly go through the theoretical procedure of using AND and OR functions, but we will first learn about AND and OR functions and their needs. Furthermore, we will discuss using AND and OR functions in Microsoft Excel for given data.

You will understand everything about AND and OR functions and how to use them after reading this blog.

So, don't you want to know how to use AND and OR functions in Microsoft excel?

Let's dive into the topic now to know more in detail.

Excel AND Function

The AND function in Excel is a logical function that is used to need multiple conditions simultaneously. AND either returns TRUE or FALSE. Use =AND(A1>0, A1<9) to see if a number in A1 is greater than zero and less than nine.

Purpose : AND allows you to test various scenarios.

Return value : If all arguments evaluate TRUE then value is TRUE; FALSE if not.

Syntax : =AND (logical[1], [logical[2], ...)

Arguments 

  • logical1 - The first condition to assess is the logical value.
  • logical2 - [optional] The second condition to evaluate is the logical value.
Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Use of AND Function

The AND function is used to verify multiple logical conditions simultaneously, up to 255 conditions supplied as arguments. Each argument (logical1, logical2, etc.) must be a TRUE or FALSE-returning expression or a TRUE or FALSE-evaluable item. Constants, cell references, arrays, and logical expressions can be used to input the AND function.

Example-

If A1 cell contains 40.

=AND(A1>0,A1>8,A1<90) // returns TRUE

=AND(A1>0,A1>8,A1<20) // returns FALSE

Note-

  • The AND function is not case-sensitive.
  • Wildcards are not supported by the AND function.
  • Arguments of text values or empty cells are ignored.
  • If no logical values are identified or created during evaluation, the AND function will return #VALUE.

 

We will see an example to understand AND function better-

Take the following data.

Enter the following formula in the B1 cell.

Now move the cursor on the vertex of the B1 cell and drag it to the B4 cell. The resulting output will be like this-

Excel OR Function

If any of the given conditions are true, the OR function in Excel returns TRUE; otherwise, it returns FALSE. Let us learn how to use the OR function in Excel by combining it with other functions.

Purpose : OR can be used to test multiple conditions.

Return value :If any of the arguments evaluate TRUE, TRUE; otherwise, FALSE.

Syntax : =OR (logical[1], [logical[2], ...)

Arguments 

  • logical1 - The first condition to assess is the logical value.
  • logical2 - [optional] The second condition to evaluate is the logical value.

Use of OR Function

If any specified parameters are TRUE, the OR function returns TRUE and FALSE if all the supplied arguments are FALSE.

The OR function examines many logical conditions simultaneously, up to 255, which are supplied as arguments. Each argument (logical1, logical2, etc.) must be a TRUE or FALSE-returning expression or a TRUE or FALSE-evaluable item. Constants, cell references, arrays, and logical expressions can all be used to input the OR function.

If A1 cell contains 40 -

=OR(A1>5,A1>67,A1>90) // returns TRUE

=OR(A1<5,A1=30,A1>90) // returns FALSE

Note-

  • Each logical condition must return TRUE or FALSE or be arrays or references with logical values.
  • Arguments of text values or empty cells are ignored.
  • If no logical values are discovered, the OR function will return #VALUE.

 

We will see an example to understand OR function better-

Take the following data.

Enter the following formula in the B1 cell.

Now move the cursor on the vertex of the B1 cell and drag it to the B4 cell. The resulting output will look like this-

FAQs

1. How can we use AND function with IF function?

The AND function can be used inside the IF function. You can supply AND as the logical test for the IF function. Now we will learn about through an example -

 

2. How can we use OR function with IF function?

OR is a function that can extend the functionality of other functions, such as the IF function. For an IF function, you can supply OR as the logical test. Now we will learn about it through an example -

3. What is the array form of the OR function?

Against a criterion, we can test all values in a range by using OR as an array formula. For example, if any cell in A1:A5 is larger than 15, this array formula will return TRUE:

4. What is a wildcard?

A wildcard character is a unique character that allows you to do "fuzzy" text matching in Excel calculations.

Example- 

= COUNTIF(A1:A5,"*ninja")

5. How can we combine the OR function with the WEEKDAY ?

In Excel, the WEEKDAY function returns a number ranging from 1 (Sunday) to 7 (Saturday) that represents the date's weekday. As a result, if the date falls on a weekend, the OR function returns TRUE.

Key Takeaways

In this article we have extensively discussed the topic of AND and OR function. Furthermore, we learned how to manipulate data using AND and OR function.

We hope that this blog has helped you enhance your knowledge regarding encryption and if you would like to learn more, check out our articles on ABS Function and Pie chart

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses and refer to the mock test and problems available. Take a look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow. 

Happy Learning!

Previous article
Comparison Operators in Excel
Next article
Contains Specific Text
Live masterclass