Select Page

### # 3 In our Top 5 Excel Functions, the Operators AND & OR

Excel AND and OR functions are fairly straightforward. In this post, we see cover details and warnings. And we wrap up with a Thought Exercise rather than examples. So, let’s hit it!

If VLOOKUP were a song, it would be Blackout Allstars’, I Like it Like That. Our exploration of Excel’s AND and OR functions is the warning in Jamiroquai’s Virtual Insanity: slow down and don’t let the big tricks overshadow and destroy the fundamentals.

### THE CASE for AND & OR (Here’s the geeky part)

In Geometry, Calculus and Logic, there are lots of exercises called Proofs. A proof starts with a claim (theorem), and then you use formal symbols and procedures to work toward a conclusion that proves the initial statement.

Then you’re thrown a curveball. You learn a lot of symbols and ways to work with the symbols and then you’re hit with a Proof that takes the form:

If you are forbidden to use any fancy or efficient symbols,
you can do everything you need to do with NOT, AND and OR.

After a painful thought process, the theorem is verified. But what’s the point to really get?

THE POINT With the right thinking process, understanding of the task, and intimacy with the basic tools, a task can be accomplished with rudimentary methods. Virtual Insanity is when we go for the big tricks or the next shiny new thing, and sacrifice the fundamentals.

We need AND and OR because other functions aren’t useful by themselves. Rarely are functions like IF and VLOOKUP ready to use straight out of the box. Yet, if we have the time, patience and strategy we could go a long way with just AND and OR.

I’m not going to take you through an Excel proof. Just understand that facility with Excel formulas can only happen when you get your ANDs and ORs straight. Almost nothing you’ll do in Excel can happen without thinking AND and OR.

### SYNTAX

 Operator Thinking in English AND Both or All =AND(Condition1, Condition2, … ) Max: 255 conditions for Excel 2007/2010 OR Either, Some or Any =OR(Condition1, Condition2 … ) Max: 255 conditions for Excel 2007/2010

With AND and OR, the result is True or False. There are no calculations

=OR(Today is Tuesday,Your name is Karen)

This is going to be TRUE if today is Tuesday or Your name is Karen.

=AND(Today is Tuesday,Your name is Karen)

This will never be true if your name isn’t Karen. If your name is Karen, this statement will only be true on Tuesdays.

Seem like much ado over nothing. But misplaced AND and OR functions can derail a task. Because AND and OR are straightforward by themselves, let’s wrap this up with a Thought Exercise. I will cover AND and OR details as we work with other functions in other posts.

How would you convert this statement into and Excel formula?

THOUGHT EXERCISE
If part #7V was shipped overnight, I will relax; but if part #7V was shipped first class or 2-Day, I will postpone my plans;
otherwise, I’ll cancel the order and buy #7V across the street