AND vs. OR | CSE 148 – Excel Exam 2

AND vs. OR explained

While AND & OR are relatively self-explanatory, here's a short story to exemplify their functionality.

Imagine you're back in middle school, and your team just won your hometown Little League baseball series. High off of the adrenaline from riding the bench while your star teammate hit the game-winning home run, your parents decide to treat you to some ice cream!

But... this isn't just any normal trip to the ice cream shop. This time, you get to pick as many toppings as you want. Time to go absolutely crazy and seize this opportunity.

AND explained

As you stand 10ft away from the cashier surveying the toppings options, you decide on crushed graham crackers and chocolate fudge drizzle. With chest puffed out with pride, you strut to the window of the shop and state the following to the cashier:

"I'd like vanilla ice cream with crushed graham crackers and chocolate fudge drizzle."

Man, it feels good to be a champion.

Story aside, what we did here was define two conditions about our ice cream that must be true to keep us happy. You must have both crushed graham crackers AND chocolate fudge drizzle to be happy with your ice cream. If either of those are missing, then you won't accept your ice cream.

In summary...

AND defines two or more conditions that must both be true in order for the function to return true. If any of the conditions are false, then the entire function returns false.

OR explained

Let's rewind back to when we were standing 10ft away from the ice cream stand, surveying our options for toppings.

This time, you take enough time scoping out the toppings options to notice how cute the cashier is. Your luck has been pretty good today with winning the series, why not go for gold?

Filled with pride and overconfidence, you boldly walk up to the counter and state the following:

"I'd like vanilla, and for my topping I'll take either gummy worms or rainbow sprinkles. Whichever is your favorite. Surprise me."

Holy sh*t. The balls on you! She's not only stunned by your confidence, but your great looks. She must be able to tell you're going to be a professional baseball player one of these days. In her daze, she responds...

"Ummmm... okay?"

Wow. She's so into you.

When you stated that you wanted "gummy worms OR rainbow sprinkles", you exemplified that you only need one of those toppings to be on your ice cream to be happy. Doesn't matter if you only get gummy worms, rainbow sprinkles, or both, you'll be happy.

OR defines two or more conditions and returns true if any of them are true. It will only return false if both the conditions are false.

Coding AND vs. OR

Let's the above scenario to an Excel worksheet to exemplify how we could accomplish it with actual code.

AND coded

Below is the template for AND functions:

=AND(logical1, [logical2], ...)

For our first order, we stated that we want both crushed graham crackers and chocolate fudge drizzle. These toppings are in cells A4 and A8. Considering that the result of our toppings are in cells D2 and E2, we'd structure our AND function like so:

=AND(D2=A4, E2=A8)

What this means is that our first topping (D2) must equal Crushed Graham Crackers (A4)...

=AND(D2=A4, E2=A8)

...and our second topping (E2) must equal Chocolate Fudge Drizzle.

=AND(D2=A4, E2=A8)

When we code this, we get the following result:

In order for it to return true, we must input the toppings we got.

Notice how if we were to switch Crushed Graham Crackers with Chocolate Sprinkles, we'd get false in return.

This is because our first logical statement is now false, so the entire AND function returns false. Remember, with AND functions all the conditions within it must be true for it to return true!

OR coded

Below is the template for OR functions:

=OR(logical1, [logical2], ...)

For our second order, we said we wanted either gummy worms or rainbow sprinkles. These toppings are in cells A5 and A2 (respectively). Considering our resulting toppings are in cells D6 and E6, we'd write our OR function like so:

=OR(A5=D6, A2=E6)

We're stating here that our first topping (D6) needs to be Gummy Worms (A5)...

=OR(A5=D6, A2=E6)

...or our second topping (E6) needs to be Rainbow Sprinkles (A2) in order for us to be happy with our order.

=OR(A5=D6, A2=E6)

When we code this, we get the following result:

It is false right now because we have not entered our result in yet. Let's do that now.

Did you notice how our OR function in cell D7 returned true right after we entered the first topping? This is because we didn't even need to enter the second topping to be happy with our order. We had already received one of our desired toppings... meaning that one of our conditions was true. With OR statements, only one of the conditions must return true for the entire function to return true!

Practice problem

You're running a fundraiser and need to achieve the following for it to be successful:

  • Raise over $1,000
  • Tim Robs donates at least $100
  • Have over 10 donors

Write an AND function in cell ___ to determine if the fundraiser successfully reached its goal.

=AND(SUM(B2:B8) > 1000, B4 >= 100, COUNT(A2:A8) > 10)

You may have been tempted to automatically think we met the fundraiser goal since the first 2 conditions were true. But remember... all conditions must be true in an AND function to return true, and in this case we didn't get more than 10 donors!

Next, let's learn about HLOOKUPs, and how they differ from VLOOKUPs!

Excel Cram Kit (Exam 2)

Want to unlock content? Get your Excel Cram Kit (Exam 2) now!

ApplyPRACTICE EXCEL EXAM 2 (PREVIEW ONLY)
ToolsExcel Nerd Notes (Exam 2) (PREVIEW ONLY)
ConceptAND vs. OR
ConceptHLOOKUP
ConceptIFERROR
ConceptNested IF vs. IFS (PREVIEW ONLY)
ConceptExcel tables (PREVIEW ONLY)
ConceptPivotTables (PREVIEW ONLY)
ConceptCOUNTIF, SUMIF, AVERAGEIF (PREVIEW ONLY)
ConceptSUMPRODUCT (PREVIEW ONLY)
Concept3D Cell References (PREVIEW ONLY)

Excel Reviews (Exam 2)

"I got this review for my 2nd CSE exam after not doing so well on the first one, and it was so helpful! Will definitely be using again in the future."

Erin Graham
Oct. 26, 2021

Rating: 5 out of 5.

Leave a Comment