OmniDocHow to use Excel AND and OR functions: Introduction to multi-condition logical judgment
Explain clearly the usage of AND and OR functions in Excel: AND requires all conditions to be true at the same time, OR requires any condition to be true, and explains how to cooperate with IF to output text results, with item-by-item verification methods.
Key points of this article
- The AND function requires all conditions to be true before returning TRUE, which is suitable for judging situations such as "passing both subjects" that are met at the same time.
- The OR function returns TRUE as long as one condition is true, which is suitable for judging situations such as "reaching excellence in any subject" that satisfy one of the conditions.
- AND and OR only return TRUE or FALSE. When you need to display text such as "passed", put them into the first parameter of IF.
- When the formula result is incorrect, first calculate each condition separately. Most problems occur when numbers and text are mixed or when cells are blank.
When making a form, several conditions often need to be met at the same time for it to count. For example, in the score sheet, "You must pass both Chinese and Mathematics to be considered passed," or in the sales sheet, orders for "Eastern China or with an amount exceeding RMB 10,000" must be marked. Many people subconsciously nest IFs, but when there are too many conditions, the formula becomes difficult to read and it is easy to miss the parentheses.
This article is written for readers who use Excel to organize data every day and can already write simple IF but are not familiar with multi-condition judgment. We'll first talk about what AND and OR do on their own, then how to use them with IF, and finally give you a checklist. After reading, you can independently write two types of judgment formulas: "satisfy both" and "satisfy either", and know where to start when the result is wrong.
Multi-condition judgment, which function is suitable? One sentence conclusion
Use AND when all conditions are required to be true, and OR when any condition is true. Both only return TRUE or FALSE. When you need to display text such as "passed", put them in IF.
| your situation | Recommended practices | reason |
|---|---|---|
| All conditions must be met at the same time, such as passing both subjects | =AND(condition 1, condition 2) | AND returns TRUE only if all parameters are TRUE |
| Just satisfy any of them, if you get 90 points in any subject | =OR(condition 1, condition 2) | OR returns TRUE as long as one parameter is TRUE |
| Need to display text such as "Passed" in the cell | =IF(AND(condition 1,condition 2),"passed","failed") | AND and OR themselves only give logical values, IF is responsible for outputting text |
| The condition contains both "and" and "or", such as (A and B) or C | =OR(AND(A,B),C) | Use parentheses to calculate the inner combination first, and then hand it over to the outer layer for judgment. |
| The same rule should be applied to an entire column of data | After the first line is written, fill it downwards | The relative reference will change with the row, and each row will automatically use the data of this row. |
Before you start: Confirm these points first
- The condition is "all" or "any". When describing conditions in Chinese, "and" and "at the same time" usually correspond to AND, and "or" and "only one" correspond to OR. Before writing the formula, write down the judgment rule in one sentence, which can save most of the rework.
- Whether the data types are consistent. Numbers should be stored as numerical values, and text conditions should be enclosed in English double quotes. If content that looks like numbers is left-aligned, it is probably text, and the result will be wrong when compared.
- How to deal with blank cells. Empty cells are calculated as 0 when participating in numerical comparison, so the result of B2>=60 is FALSE when B2 is empty. If blanks should be excluded, treat them separately in the formula, or complete the data first.
- Whether the operator is written correctly. Greater than or equal to is written as >=, not equal to is written as <>. When an operator is written incorrectly, Excel may report an error or silently give unexpected results, which is harder to detect.
- Whether to retain the original data. It is recommended to write the formula in a blank column without directly overwriting the original data, which is convenient for verification and easy to roll back at any time.
Steps to use AND and OR to determine multiple conditions in Excel
The following example assumes that column A is the name, column B is the Chinese score, column C is the math score, row 1 is the header, and row 2 onwards is the student data.
- Confirm the header and data range. Check that the results are in columns B and C, the data starts from row 2, and confirm that there is no interference from merged cells.
- Write an AND formula first. Enter =AND(B2>=60,C2>=60) in D2 and press Enter. TRUE is displayed when both subjects are not less than 60, otherwise FALSE is displayed.
- Write another OR formula. Enter =OR(B2>=90,C2>=90) in E2 to identify students who have scored 90 or more points in any subject.
- Use IF to convert logical values into text. Enter =IF(AND(B2>=60,C2>=60),"passed","failed") in F2, the result is clear at a glance.
- Fill down to the last row of data. Select D2 to F2, drag the fill handle in the lower right corner to copy downward. B2 and C2 in the formula will automatically change to B3 and C3, corresponding to the next student.
- Add text conditions. If you only want to filter out the records of a certain student with passing grades, you can write =AND(A2="Zhang San",B2>=60). Text conditions must be placed in English double quotes.
- Use parentheses to group multi-level conditions. Assuming that column G is the English score, to judge "90 points in Chinese or mathematics and passing English", you can write =AND(OR(B2>=90,C2>=90),G2>=60). Inner conditions in parentheses are evaluated first.
Check each item after completion
After getting the result, don’t rush to believe the TRUE or FALSE in front of you. It is recommended to go through the following table.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| Whether the logical value is as expected | Pick two or three rows of original data and manually compare whether they are consistent with TRUE or FALSE | Check whether the brackets are paired and whether the operators are written correctly |
| Are the boundary values correct? | Find a record with a score of exactly 60 or 90 and see if the result complies with the rules. | If >= is mistakenly written as >, the boundary value will be missed, change it back to >= |
| Whether blank lines are misjudged | Filter out blank cells and view their formula results | Add judgment in the outer layer, for example =IF(B2="","",AND(...)) |
| Does the text condition match? | View text records with a result of FALSE and check if there are any extra spaces | Use the TRIM function to clear spaces or unify the input format |
| Is the reference misplaced after filling? | Click on any cell below to see if the row number in the formula changes accordingly. | If the line number is fixed, check whether absolute references are misused, such as $B$2 |
| Is the nested structure clear? | Read the formula layer by layer and confirm the range of brackets at each layer. | If the formula is too long, split it into several columns and calculate it step by step. Once the results are correct, merge them again. |
If the amount of data is large and cannot be checked line by line, you can use the filter function to spot-check a dozen records, paying special attention to boundary values and blank lines.
📊 Core comparison
| function combination | Return results | Typical uses | Common misunderstandings |
|---|---|---|---|
| AND | TRUE or FALSE | Conditions that must be met at the same time, such as passing both subjects | Mistakenly thinking that if a condition is met, TRUE will be returned. |
| OR | TRUE or FALSE | It is sufficient to meet one of the conditions. If any one of the subjects reaches the standard | When you want to express "neither is satisfied", just use OR, but the result is the opposite. |
| IF set AND or OR | The text or numbers you set | Provide conclusion columns directly in the table | The condition is long and the number of parentheses is difficult. It is recommended to divide it into several columns and calculate it step by step. |
AND and OR are responsible for "judgment", and IF is responsible for "output". Do the judgment part correctly first, and then apply IF. It is easier to troubleshoot than writing the complete long formula at once.
❓ Frequently Asked Questions (FAQ)
1. Can the excel or function and the and function be mixed?
able. OR can be put into the parameters of AND, and AND can also be put into the parameters of OR. Just use brackets to indicate the order of calculation.
2. The AND function returns FALSE, but I think it should be TRUE. How to check?
First calculate each condition separately, for example, just write =B2>=60 in a blank cell, and view the results item by item. In most cases, the numbers are saved as text, or there are invisible spaces in the cells.
3. When there is only one condition, do I need to use AND?
unnecessary. A single condition can be written directly in IF, for example =IF(B2>=60, "pass", "fail"). AND or OR is only needed when multiple conditions need to be evaluated simultaneously.
4. Can I use only one formula to determine whether multiple conditions are met without adding IF?
Can. AND and OR themselves return TRUE or FALSE, so they are suitable for use as auxiliary columns and can also be used with filtering. IF is only needed if you need to display text like "passed".
📝 Summary
AND manages "all are true" and OR manages "any one is true". Together with parentheses and IF, they can cover most multi-condition judgments. After writing the formula, manually check it with a few lines of real data, which saves time than repeatedly revising it.
