EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Excel Excel Resources Logical Functions in Excel Tutorial Logical Functions in Excel

Logical Functions in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated August 19, 2023

Logical Functions in Excel

Excel Logical Functions (Table of Contents)
  • Introduction to Logical Functions in Excel
  • Types of Logical Functions in Excel

Introduction to Logical Functions in Excel

There are various types of logical functions, some commonly used in Excel data operations. Logical functions in Excel work with conditions. They help deal with different situations accordingly. These functions perform work similar to IF-ELSEIF-ELSE statements in a programming language. These functions have simple syntaxes and can be beautifully combined with other functions to obtain desired results.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Types of Logical Functions in Excel

Excel’s various types of logical functions are IF, IFERROR, AND, OR, NOT, TRUE, and FALSE. In the following section, we will see how to use each through examples.

You can download this Logical Functions Excel Template here – Logical Functions Excel Template

1. IF

A simple logical function checks whether to validate the condition as true or false. We will demonstrate the IF function using a very simple example. There’s a simple dataset containing data on students and their marks. Now, if the passing marks are 40, we would like to declare each student’s result in PASS and FAIL. This can be done using the IF function.

Syntax: 

IF Logical 1-1 (Logical Functions in Excel)

Example:

Step 1: When we enter the IF function, Excel automatically pops up the list of arguments, as shown in the following screenshot.

IF Logical 1-3 (Logical Functions in Excel)

Step 2: The first argument is the logical test. In this case, the condition is that if marks are more than 40, then the result is PASS, or else it is FAIL. As shown below, we passed the condition as the argument in the IF function.

IF Logical 1-4

Step 3: The second and third arguments are ‘value_if_true’ and ‘value_if_false’. If marks exceed 40, the result should be PASS; otherwise, FAIL. This has been accomplished by passing the arguments shown in the following screenshot.

IF Logical 1-5

Step 4: Copy the IF function across all the required cells, and we returned the correct result as shown in the following screenshot.

IF Logical 1-6

2. IFERROR

There are various errors in Excel, and IFERROR uses to deal with these errors. Many times, in Excel, we are returned error values as results. In such cases, the IFERROR function becomes handy. We use this function when we want to return a meaningful result rather of an error.

Syntax: 

iferror syntax

As we can see in the syntax, the IFERROR function basically means that if the value passed into it is not an error value, then that value will be given as output by the function; otherwise, the value we specified in the ‘value if error’ argument.

Step 1: The IFERROR function considers two arguments in the syntax. This is also shown in the screenshot below.

IFERROR 2-1

Step 2: In this example, we have marks for certain students. For some of the students, the result is not available, and for them, we have a #N/A error in the Marks column. We want this error value to be replaced with some meaningful text possible through IFERROR. The following screenshot shows this.

IFERROR 2-2

As we can see in the above screenshot, we passed marks as the first argument and kept the string “Not Available” as the second argument. It means if marks are valid, output marks or “Not Available”. This is shown in the following screenshot.

IF ERROR 2-3

3. AND

This function returns either a TRUE or FALSE value by checking the conditions passed into it. If all the conditions passed into AND function are met, then the TRUE value is returned, and if even a single condition is not met, then the FALSE value is returned.

Syntax: 

AND -Logical function 3-1

Step 1: We have marks for three different subjects for certain students. Now, we want to check if the student has passed or failed. If a student secures marks less than 40 even in a single subject out of the three subjects, he has failed the examination. We can determine the result for each student using the AND function. When we enter the AND function, we find that arguments are highlighted. In this case, they are logical conditions, as shown below.

AND -Logical function 3-2

Step 2: Using AND, we will get results for each student by passing the conditions in the function as shown below.

AND -Logical function 3-3

When the function is implemented, we get the result as shown below. Note, here, TRUE means pass, and FALSE means fail.

AND -Logical function 3-4

4. OR

It returns a TRUE value even if a single condition is met out of the total conditions passed. It returns FALSE when all the arguments are FALSE.

Syntax: 

OR-Logical Function in excel 4-1

Step 1: We have marks for three different subjects for certain students. Now, if a student has secured 40 marks in at least one of the three subjects, the result should be a pass or a fail. When we enter the OR function, the arguments are highlighted below.

OR-Logical Function in excel 4-2

Step 2: The desired criteria for determining a student’s result can be met using the OR function below.

OR-Logical Function in excel 4-3

Following the above step, we get the desired result, as shown below. Observe it.

OR-Logical Function in excel 4-4

5. NOT

It reverses the logical value, i.e., TRUE to FALSE and FALSE to TRUE. It is usually implemented in context-based situations.

Syntax: NOT(logical)

NOT -Logical Function 5-1

Step 1: We pass certain logical conditions into NOT and check how the function works. Have a look at the following screenshot.

NOT -Logical Function 5-2

The actual result for each of them is shown. However, the logical values are reversed when we pass the logical conditions into the NOT function, as shown below.

NOT -Logical Function 5-3

6. TRUE and FALSE

They return corresponding logical values; TRUE returns TRUE, while FALSE returns FALSE.

Syntax: TRUE(), FALSE()

TRUE and False 6-1

We have the result of the students. For PASS, we will display TRUE, and for FAIL, FALSE. The following screenshot shows the implementation.

TRUE and False 6-2

The result that we obtained is shown below.

TRUE and False 6-3

Things to Remember

  •  You should implement the logical functions in various contexts to understand their full capability.
  • You should check the difference in available features in logical functions for different versions of Excel.
  • Each argument in a logical function should be studied to understand its role and significance.

Recommended Articles

This has been a guide to Logical Functions in Excel. Here we discuss How to use Logical Functions in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Excel Logical Test
  2. Excel IF AND Function
  3. AND Function in Excel
  4. Excel SUMIF with OR
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Logical Functions Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA

Download Logical Functions Excel Template

EDUCBA

डाउनलोड Logical Functions Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW