Introduction

Welcome! IF, AND, OR, NOT & IS Functions

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Excel Expert Level 3. In this course we will focus on logic functions in Microsoft Excel, including understanding true and false values, and working with the AND, OR, and NOT functions. We will discuss the IF function to make decisions in spreadsheets, explore nested IF statements, assign letter grades, and handle time calculations like overnight shifts and overtime pay. We will also talk about the IS family of functions, such as ISBLANK, ISERROR, ISEVEN, ISLOGICAL, ISNUMBER, ISTEXT, ISNONTEXT, and IFERROR. This course is intended for experienced users who have completed the previous expert levels.

Navigation

Prev: X03
Next: Lesson 01

Keywords

TechHelp Excel, Excel 2010 logic functions, IF function, AND function, OR function, NOT function, nested IF, ISBLANK, ISERROR, IFERROR, ISNUMBER, ISTEXT, ISLOGICAL, ISEVEN, ISODD, timesheet over midnight, calculate overtime, letter grade formula

Subscribe to Introduction
Get notifications when this page is updated

More Information

Transcript

Welcome to Excel 2010 Expert Level 3, brought to you by ExcelLearningZone.com. I am your instructor, Richard Rost.

Today's class is all about logic functions in Excel. We are going to learn about true and false values, the AND, OR, and NOT functions.

We are going to learn one of the most powerful functions in Excel, the IF function, which can be used to check conditions - if this then do that. We are going to begin to make our spreadsheet smart by learning about the IF function.

Then, finally, we are going to learn about a group of functions called the IS functions that can be used to check for certain conditions, like whether or not a cell is blank.

This class is designed to be used with Excel 2010, part of Microsoft Office 2010. If you are using an older version of Excel, you should visit my website at ExcelLearningZone.com and look for my older tutorials covering Excel 2000 through 2007. If you are using Excel 2007, you should have little difficulty following along as most of the concepts covered in this course are the same.

If you are using an older version like 2003 or earlier, you really should either upgrade to Excel 2010 or purchase a tutorial for your version of Excel, which again you can find on my website.

This is an expert level class for Microsoft Excel 2010. It was designed for the user who has a good amount of experience with Microsoft Excel. The prerequisite for this course is my Expert Level 2 course. You should have taken that course previously. It covers Excel dates and times, custom date codes and formats, and many of the popular date and time functions.

If you have not taken Expert Levels 1 and 2, and my beginner level series, I strongly recommend you go to my website at ExcelLearningZone.com and look for those courses.

My courses are broken up into four different groups: beginner, expert, advanced, and developer.

My beginner courses are for novice users who have little or no experience with Microsoft Excel. They are designed to give you an overview of the basic features and cover just what you need to know to be productive.

The expert series, which is what you are watching right now, is designed for more experienced users who are already comfortable with Excel. Expert classes go into a lot more depth about each topic than the beginner classes did, and will cover more functions, features, tips, and techniques for power users.

After you have mastered the expert classes, move up to the advanced lessons. You will learn how to record macros, build user forms, create your own templates, and many more advanced features that not everyone will use, and they really add enhanced functionality and professionalism to your spreadsheets.

Finally, my developer level courses will teach you how to program in Visual Basic for Applications for Microsoft Excel. This will allow you to create Excel-based programs for your users, automate your spreadsheets, and integrate Excel tightly with the other Microsoft Office applications.

Each of my series is broken down into different levels. For example, the beginner series contains five different levels. This class is the third level of the expert series. Each level teaches you new and different topics in Microsoft Excel, building on the lessons learned in the previous levels.

When you have finished all the expert classes, you will move up to the advanced series, then finally the developer series.

Now, let's take a more detailed look at exactly what we are going to cover in today's class.

In lesson one, we are going to learn about logic functions: TRUE, FALSE, AND, OR, and NOT.

In lesson two, we are going to learn one of the most powerful functions in Excel, the IF function.

In lesson three, we are going to look at a couple more examples with the IF function. We will see how nested IF functions work. We will assign a letter grade to our students. We will fix our timesheet that we worked on in the last class, where we can now have times that go over midnight, so you can clock in at 11 p.m. and clock out at 3 a.m. We will see how to calculate overtime pay based on a 40-hour work week.

In lesson four, we are going to learn about the IS family of functions: ISBLANK, ISERROR, IS EVEN or ODD, ISLOGICAL, ISNUMBER, ISTEXT, ISNONTEXT, and then one that does not start with IS, which is IFERROR.

If you need help with the topics covered in today's lessons, please feel free to post your questions in the Excel interactive student forums.

If you are watching this course using my custom video player software or online in my web theater, you should see the student forum for each lesson appear in a small window next to the class videos, if you have an active internet connection.

Here, you will see all of the questions that other students have asked, as well as my responses to them, and any other comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to post your own questions and comments as well.

If you are not watching your lessons online, you can still visit the student forums later by visiting ExcelLearningZone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then replay the lesson from the beginning and follow along with my examples. Actually, create the same spreadsheet that I make in the video. Build a spreadsheet with me step by step.

Do not try to apply what you are learning right now to other projects until you have mastered the sample spreadsheet. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forums.

Most importantly, keep an open mind. Excel might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Intro

Quiz

Q1. Which function in Excel is used to make decisions based on conditions?
A. IF
B. SUM
C. VLOOKUP
D. COUNTIF

Q2. What are the three main logic functions discussed in this lesson?
A. AND, OR, NOT
B. SUM, COUNT, AVERAGE
C. IF, ISERROR, CONCATENATE
D. VLOOKUP, HLOOKUP, MATCH

Q3. What values do the logical functions TRUE and FALSE return in Excel?
A. Boolean values
B. Numeric values only
C. Text strings only
D. Cell references

Q4. What is the function of the AND function in Excel?
A. Returns TRUE if all conditions are true
B. Returns TRUE if any one condition is true
C. Returns the opposite of a logical value
D. Adds numeric values

Q5. What does the OR function do?
A. Returns TRUE if at least one of the conditions is true
B. Returns TRUE if all conditions are false
C. Always returns FALSE
D. Returns the opposite of the NOT function

Q6. Which of the following would you use to reverse the logical value of its argument?
A. NOT
B. OR
C. AND
D. ISBLANK

Q7. Which function is most effective for checking if a cell is blank?
A. ISBLANK
B. COUNTIF
C. SUMIF
D. NOT

Q8. What is the function IFERROR used for in Excel?
A. To return a specified value if a formula results in an error
B. To count the number of errors in a worksheet
C. To highlight errors in red
D. To display formulas instead of values

Q9. What is the recommended order for watching the courses mentioned in the video?
A. Beginner, Expert, Advanced, Developer
B. Advanced, Expert, Beginner, Developer
C. Developer, Beginner, Advanced, Expert
D. Expert, Advanced, Beginner, Developer

Q10. What should you do if you have questions about lesson topics?
A. Use the Excel interactive student forums
B. Wait until the end of the course
C. Email Microsoft support
D. Only consult printed manuals

Q11. According to the video, what is suggested before trying to apply new concepts to your own projects?
A. Practice and complete the sample spreadsheet first
B. Read the user manual
C. Watch unrelated tutorials
D. Try to customize immediately

Q12. What will you learn to calculate in lesson three of this course?
A. Overtime pay based on a 40-hour work week
B. Loan interest rates
C. How to sort and filter data
D. How to create charts

Q13. Which logical test would help you determine if a value is a number?
A. ISNUMBER
B. ISTEXT
C. ISBLANK
D. IFERROR

Q14. What are the four groups Richard Rost divides his Excel courses into?
A. Beginner, Expert, Advanced, Developer
B. Novice, Intermediate, Advanced, Professional
C. User, Designer, Programmer, Administrator
D. Entry, Skilled, Pro, Elite

Q15. What does nesting IF functions allow you to do?
A. Test multiple conditions in sequence
B. Add several numbers together
C. Sort data by more than one column
D. Insert images into a spreadsheet

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Excel Learning Zone focuses on logic functions in Excel 2010. If you have been working with Excel for a while and are ready to make your spreadsheets more dynamic and responsive, this class covers the tools you will need. We will be looking at true and false values, how to use the AND, OR, and NOT functions, and perhaps the single most powerful tool for adding logic to your sheets: the IF function.

The goal for today's lesson is to help you get comfortable with adding conditional logic to your spreadsheets. We will go into detail on how these logical functions work, including building more advanced formulas by combining functions together. Once you master these, you will be able to make your spreadsheets "smart" - that is, they will be able to react to the data within them.

This course is designed specifically for users of Microsoft Excel 2010, which is included with Office 2010. If you are working with Excel 2007, most of what I demonstrate should also work for you. For those using much older versions like Excel 2000 or 2003, it is a good idea to look for a course specifically for your version, or even consider upgrading to get all the newest features.

This is part of the expert level courses, and you will get the most out of these lessons if you already have a solid understanding of Excel's basics and have completed my earlier Expert Level 2 course, which covers dates, times, and many related functions. If you are completely new to Excel, or have not yet covered the beginner levels, check out those first to build your foundation.

All of my courses are organized into four levels: beginner, expert, advanced, and developer. The beginner lessons help new users get comfortable with the essentials. The expert lessons, like this one, are for those who are already familiar with the basics and are looking to tackle more challenging material to really harness Excel's power. When you're ready, the advanced lessons will teach you how to create macros, design custom forms, build templates, and use other features that take your work to the next level. Eventually, if you want to automate your work even more or integrate Excel with the rest of Microsoft Office, the developer series will introduce you to programming in Visual Basic for Applications (VBA).

Each series contains several different levels. This particular class is the third in the expert level series. The material in each level builds on the previous classes, so be sure to go through them in order to make the learning process smoother.

After you complete the expert series, move on to the advanced and then developer series for even more powerful skills.

Here's what we'll be covering today. In the first lesson, we will learn about Excel logic functions: TRUE, FALSE, AND, OR, and NOT. Next, we will be diving into the IF function, which lets you perform actions depending on whether a certain condition is true or false.

In the third lesson, we will take the IF function further, looking at more complex examples like nesting IF statements inside of one another, assigning letter grades to students, and solving practical problems such as timesheet calculations that cross over midnight - where you might clock in late at night and out early in the morning. You'll also see how to compute overtime for a 40-hour work week.

The fourth lesson will introduce the IS family of functions - ISBLANK, ISERROR, ISEVEN, ISODD, ISLOGICAL, ISNUMBER, ISTEXT, ISNONTEXT, and a special function called IFERROR, which helps with error handling.

If you have questions while you're studying these topics, you can always use the interactive student forums, especially if you're watching through my custom video player or on the web. The forum displays next to each lesson, showing questions from other students, my responses, and discussions among your peers. If you're not online, you can visit ExcelLearningZone.com/forums later to join the conversation.

To get the most benefit from this course, I recommend that you first watch each lesson all the way through without trying to follow along on your computer. Once you've watched it, replay the lesson and work along with me, building your spreadsheet step by step. This hands-on practice will really help all the concepts stick. Try to focus on the practice spreadsheet first, rather than jumping straight to your own projects. If you run into trouble, review the lesson again or post your questions in the student forum.

Keep an open mind as you work through these lessons. Excel can look intimidating at first, but once you grasp these logic functions and practice a little, you'll find that you can use them to solve real-world problems and make your spreadsheets much more effective.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Logic functions TRUE and FALSE
Using the AND function
Using the OR function
Using the NOT function
Introduction to the IF function
Using IF to check conditions
Nested IF statements
Assigning letter grades with IF
Fixing a timesheet for overnight hours
Calculating overtime pay with IF
Using ISBLANK function
Using ISERROR function
Using ISEVEN and ISODD functions
Using ISLOGICAL function
Using ISNUMBER function
Using ISTEXT function
Using ISNONTEXT function
Using IFERROR function

Article

Welcome to an expert-level tutorial on logic functions in Microsoft Excel. In this lesson, you will learn how to use logical functions to make your spreadsheets smarter and more interactive. We will cover TRUE and FALSE values, how to use the AND, OR, and NOT functions to build custom logic, and one of the most important tools in Excel, the IF function. We will also explore a powerful group of functions called the IS functions which allow you to check whether certain conditions are met in your cells.

To get started, it is helpful to understand what logic functions do in Excel. Logic functions allow you to test whether certain things are true or false. For example, you might want to know if a number in a cell is greater than a certain value, or whether two conditions are true at the same time. Excel uses the Boolean values TRUE and FALSE to represent these kinds of answers.

The AND function checks if all the conditions you specify are true. For example, if you want to know if cell A1 is greater than 10 and cell B1 is less than 20, you could use the formula AND(A1>10, B1<20). Excel will return TRUE only if both conditions are true.

The OR function, on the other hand, checks if at least one of the conditions you specify is true. For example, if you want to know if cell A1 is greater than 10 or cell B1 is less than 20, use the formula OR(A1>10, B1<20). This will return TRUE if either condition is true.

The NOT function takes a logical value or test and reverses it. So if your formula is NOT(A1>10), it will return TRUE if A1 is not greater than 10 and FALSE if it is.

Now let us talk about the IF function. This is one of the most powerful and useful tools in Excel. It allows you to check a condition and take different actions based on whether the condition is true or false. The basic syntax of the IF function is IF(logical_test, value_if_true, value_if_false). For example, IF(A1>50, "Pass", "Fail") will display "Pass" if the value of A1 is greater than 50, or "Fail" if it is not.

You can also nest IF functions inside each other for more advanced logic. For example, to assign a letter grade based on a test score, you might use a formula like IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", IF(A1>=60, "D", "F")))). This formula checks each condition in order and returns the corresponding grade.

It is also possible to use the IF function to update an existing spreadsheet. Suppose you have a timesheet and you need to calculate the hours worked, even if someone clocks in before midnight and out after midnight. You could use the formula IF(End<Start, (End+1)-Start, End-Start) where Start is the time in and End is the time out. This will handle times that cross past midnight correctly.

Another practical example is using the IF function to calculate overtime. For instance, suppose you pay overtime for any hours worked over 40 in a week. You could use IF(TotalHours>40, (TotalHours-40)*OvertimeRate + 40*RegularRate, TotalHours*RegularRate). This checks if the total hours are more than 40 and calculates the overtime hours separately.

Excel also provides a group of functions called the IS functions. These allow you to check the type of data in a cell, or whether a certain condition exists. For example, ISBLANK(A1) returns TRUE if A1 is empty. ISERROR(A1) checks if a cell contains an error. You can use ISEVEN(A1) or ISODD(A1) to check if a value is even or odd. ISLOGICAL(A1) tells you if a cell contains a logical value, such as TRUE or FALSE. ISNUMBER(A1) checks if the cell contains a number, while ISTEXT(A1) checks if it contains text. ISNONTEXT(A1) is the opposite, returning TRUE if a cell does not contain text. There is also the IFERROR function, which allows you to return a custom value if your formula results in an error. For instance, IFERROR(A1/B1, "Error") will attempt to divide A1 by B1, but if there is an error, such as dividing by zero, it will return the text "Error".

For best results, practice each formula with sample data. Recreate example spreadsheets and try different scenarios, such as testing nested IFs or combining AND and OR functions inside your IF formulas. If you make a mistake or get an unexpected result, review your logic and try to break down the formula into smaller pieces to see what each part returns.

By mastering the logical and IS functions in Excel, you will greatly increase your power to design worksheets that make decisions, check for mistakes, handle exceptions, and automate repetitive tasks. With regular practice, using these functions will become second nature, and you will be able to solve many complex spreadsheet problems quickly and efficiently. If you find yourself stuck, do not hesitate to reach out to Excel communities or forums online, as it is very likely others have worked through similar issues before. Keep an open mind, experiment freely, and enjoy making your spreadsheets work smarter for you.

Primary Topics

logic functions, IF function, AND, OR, NOT, IS functions, error handling, nested IF, spreadsheet automation

Secondary Topics

course progression, learning strategies, course structure