AND and OR in Excel: conditions, intervals and IF

“At least 80 points and attendance” and “at least 80 points or attendance” produce very different pass lists. One word changes which people qualify. In Excel, AND and OR express that difference.

I start by writing the rule in an ordinary sentence and then translate it into a formula. I will use the same small synthetic table for both functions before moving to numerical intervals and nested rules. The goal is to explain each decision rather than trust a formula merely because it shows no error.

Every condition or at least one

AND returns TRUE when every condition is true. One FALSE is enough to make the combined result FALSE. OR needs at least one TRUE. When both conditions are true, OR also returns TRUE.

Ordinary OR therefore does not mean “exactly one”. Meeting both requirements still meets an “at least one” rule. Microsoft defines these behaviors in the AND reference and OR reference.

You do not need to memorize the table. For AND, ask whether any requirement failed. For OR, ask whether any alternative succeeded. Those two questions are enough to follow a small example all the way through.

The label in a report should reflect the question. TRUE might mean “eligible” in one column and “needs inspection” in another. Without a clear heading, a perfectly correct logical result can still confuse the reader.

One dataset, two different rules

This dataset is synthetic. Put the headers in A1:C1 and the records in rows 2:5. Column C contains the logical values TRUE and FALSE, entered without quotation marks. Enter the threshold 80 in H2.

The first rule requires at least 80 points and attendance:

=AND(B2>=$H$2,C2=TRUE)

After filling down, only Ada receives TRUE. Ben has enough points but no attendance. Celina attended but has too few points. Dan meets neither requirement. Each FALSE can be explained by pointing to a particular condition.

The alternative rule allows either requirement:

=OR(B2>=$H$2,C2=TRUE)

Ada, Ben and Celina now receive TRUE. Dan still receives FALSE. Replacing the function without agreeing on the rule changes the meaning of the report. Put the two result columns side by side and identify which people switch groups.

I use English names and commas here. In Polish Excel the functions are ORAZ and LUB; argument separators depend on regional settings. Each comparison is an argument inside the function. Do not insert words such as AND between comparisons as though Excel were reading an ordinary sentence.

Use IF for readable labels

Logical values are useful while checking a rule. A report can present a descriptive label instead:

=IF(AND(B2>=$H$2,C2=TRUE),"Passed","Not passed")

AND determines whether the conditions hold. IF selects the text from that result. Keeping these as two visible steps can help while learning, and a separate test column may also make a real workbook easier to explain.

The IF guide covers outcome types and missing data. Here the key point is that IF receives one final logical test even when that test combines several comparisons.

The dollar signs keep H2 fixed as the shared threshold. B2 and C2 should move to the next row when copied. I inspect the first copied formula to confirm that it reads the next person’s data rather than repeating a decision about the first person.

Use AND inside an interval

Suppose valid numerical results run from 80 to 100 inclusive. Both boundaries must hold:

=AND(B2>=80,B2<=100)

A value of 85 qualifies. Values of 70 and 120 fail. Both 80 and 100 qualify because equality is included. For an open boundary, change the relevant comparison to > or <.

The expression 80<=B2<=100 is not a safe shorthand for two comparisons in Excel. Split the interval into two explicit tests. Each fragment then corresponds to one requirement that another reader can understand independently.

Using OR(B2>=80,B2<=100) by mistake would accept every ordinary number: below 80 the second test is true, and above 100 the first is true. An obviously out-of-range value exposes this error much more clearly than another example of 85.

Use OR outside an interval

To flag invalid results, ask whether the number is below 80 or above 100:

=OR(B2<80,B2>100)

Now 70 and 120 return TRUE, while 80, 85 and 100 return FALSE. The accepted interval has stayed the same. The question changed from “is it inside?” to “does it require inspection?”. The column heading should make that change visible.

This is a useful review exercise because it requires no complicated calculation. List the two boundaries and a value on each side. If the answer surprises you, return to the sentence describing the rule before changing any brackets.

Group more complex rules deliberately

Consider another synthetic rule: attendance is mandatory, and the person must also have at least 80 points or a project marked Completed in D2.

=AND(C2=TRUE,OR(B2>=$H$2,D2="Completed"))

The outer AND keeps attendance mandatory. The inner OR provides two ways to meet the second requirement. Without attendance, a completed project cannot make the outer result true.

Rearranging the functions can express a different policy. Putting the completed project into an outer OR could allow someone to qualify without attending. I list mandatory requirements first and alternatives second, rather than trying to guess the intended policy from a long expression.

Every group of brackets should correspond to a meaningful part of the sentence. If the sentence remains ambiguous, stop before building the formula. Excel cannot decide whether a project should waive attendance; that decision belongs to the owner of the rule.

For a rule maintained by several people, helper columns can make each component visible. They take more space but can reduce the cost of explaining why a particular person qualified. Compactness is useful only while the calculation remains understandable.

Text, empty cells and errors

Microsoft states that text and empty cells in reference or array arguments can be ignored by AND and OR. A range with no logical values can produce an error. I therefore avoid treating AND(B2:C2) as validation of raw imported data.

Explicit comparisons are clearer. C2=TRUE checks a logical value. The word "Yes" is text and needs a different comparison. Choose one convention for a column instead of mixing checkboxes, blanks and several textual responses.

A missing points value needs its own policy too. Use ISNUMBER to inspect types where appropriate and show a separate status for missing data. Also avoid assuming that adding an error check as another AND argument protects every subsequent expression. Logical functions are not a general error-catching mechanism.

These distinctions matter when results are copied into another report. A FALSE caused by a missing measurement may require action, while a measured result below the threshold may be an ordinary business outcome. A separate quality status can preserve that difference.

Exercise: find the case that changes the answer

Change Ben’s score to 80. Under AND he still fails because he did not attend; under OR he still qualifies through the points condition. Then change the score to 79. He should lose the OR qualification too. That shows the threshold and the second condition working together.

Add one person with 100 points and another with 101. For the 80-100 interval, the first should qualify and the second fail. Run the outside-interval test as well: the answers should reverse for these valid numerical inputs.

For totals based on qualifying transactions, continue to SUMIFS. First make sure the selection rule for a single row is clear. A useful aggregate depends on a well-defined population.