Logical or conditional functions in Excel or spreadsheets: AND, OR and IF

14/07/26 •0•0

In the first class of Advanced Computing 2, we will see the Excel formulas "OR" and "AND" and how they are applied within other functions such as IF

Geschrieben vonLeon

Übersetzt vonEditorial

Überarbeitet vonEditorial

The Advanced Computing 2 course assumes that the student has read and understood the Advanced Computing 1 course. In course 1, we saw how spreadsheet applications, using Excel as an example, work: their layout, how to write, how to do calculations, and their functions.

A brief summary

- In spreadsheet applications like Excel, we write formulas in a cell;

- In the cell, to write a function or a mathematical sum, you must start with "=";

- Inside cells, after an equals sign, plain text must be written in quotation marks;

- If you want to build a function, you must write the name of the function and immediately after: open parentheses, write the function arguments (inputs) separated by ";" and close parentheses.

Notice in the example above, first the equals sign ("=") was written, then the IF function was written. The IF function takes 3 arguments: a statement that can be true or false; what happens if it is true; what happens if it is false. In the example, the statement is "B1 > C1" and it will return "YES" if true and "NO" if false. B1 is 21 and C1 is 1, so 21 > 1, which makes the statement true, returning "YES".

Shall we learn more functions then?

The AND function

The AND function is used to test whether the arguments involved are true or not. The result of this function will return true only if all the arguments that make up the function are true; just one argument being false is enough for it to return false.

In mathematics, the AND function would be the intersection between two groups. In this case, only the values that belong to both groups are part of the intersection.

We also see this function appearing in "truth tables" when studying mathematical logic, especially for public service exams.

In Excel:

=AND(argument 1; argument 2; argument 3; ...)

Argument in this case is a logical test, for example: "A5 = 10" or "A6 > 18".

Exercise

Identify the people who are single and are 22 years old or older, and married and older than 21 years old.

Name	Marital Status	Age
Caio Mansera	Married	36
Raul Guedes	Single	22
Antônia Orsolato	Married	25
Quênia Rhuby	Married	26
Fábio Souza	Single	28
Danielle Farrine	Single	21
Edivaldo Chopp	Single	43
Césas Alfeir	Married	22
Paschoal Ranieri	Single	26

Solution

=AND(B2="Single";C2>=22)

=AND(B2="Married";C2>21)

The OR function

Like the AND function, the OR function is also used to test whether the arguments involved are true or not. The result of this function will return true if at least one of the arguments that make up the function is true. All arguments must be false for it to return false.

In mathematics, the OR function would be the union between two groups. In this case, any value that belongs to the groups is part of the union.

In the truth table, the union is seen this way:

In Excel:

=OR(argument 1; argument 2; argument 3; ...)

Exercise

Identify the students who have grade 1 or grade 2 above 7.

Name	Grade 1	Grade 2
Caio Mansera	7.5	7
Raul Guedes	8	7.4
Antônia Orsolato	6	7
Quênia Rhuby	6	5
Fábio Souza	10	3
Danielle Farrine	4	4.5
Edivaldo Chopp	8	5
Césas Alfeir	6	7
Paschoal Ranieri	4.5	6.5

Solution

=OR(B2>7;C2>7)

The IF function aligned with OR

Exercise

There are two grades for each student. Those who have at least one of the grades above 7 should be identified with the message "Passed"; if both grades are equal to or less than 7, the message "Exam" should be shown.

Solution

=IF(OR(B2>7;C2>7);"PASSED";"EXAM")

The IF function aligned with AND

Exercise

In a spreadsheet related to a company's HR department, you will have to identify single people over 21 years old as "group 1"; married people over 21 years old as "group 2"; and if they do not satisfy the mentioned conditions, the message "Not classified" will be displayed.

Solution

=IF( AND(B2="Single"; C2>21); "Group 1"; IF( AND( B2="Married"; C2 > 21); "Group 2"; "Not classified"))

One more example

Exercise

If a salesperson manages to sell more than R$125,000.00 in merchandise, they will earn a bonus of 12% of the sale value. But, since there is less activity in the South region, it was accepted that, if the salesperson resides in the South region, they will earn the bonus if they sell more than R$100,000.00 in sales.

Salesperson	Region	Sales
Salesperson 1	South	R$ 87,925.00
Salesperson 2	North	R$ 129,887.00
Salesperson 3	Southeast	R$ 12,908.00
Salesperson 4	South	R$ 99,087.00
Salesperson 5	Northeast	R$ 234,987.00
Salesperson 6	North	R$ 10,000.00
Salesperson 7	South	R$ 105,000.00
Salesperson 8	Midwest	R$ 1,876,098.00
Salesperson 9	Northeast	R$ 12,387.00
Salesperson 10	South	R$ 12,876.00
Salesperson 11	Southeast	R$ 123,865.00
Salesperson 12	Midwest	R$ 128,543.00
Salesperson 13	North	R$ 987,000.00

Solution

=IF( OR( C2 > 125000; AND( B2 = "South"; C2 >= 100000)); C2 * 0.12; "No bonus")

Exercises

1. Given a student's grade in cell A1 and the number of absences in cell B1, write a formula that:

- Returns "Passed" if the grade is greater than or equal to 7 and the number of absences is less than or equal to 5.

- Returns "Recovery" if the grade is greater than or equal to 5 and the number of absences is less than or equal to 5.

- Returns "Failed" otherwise.

2. Given the values in the cells: A1 (Monthly Income); B1 (Credit Score); and C1 (Has Restrictions on Name: "Yes" or "No"). Write a formula that:

- Returns "Approved" if the income is greater than 3000, the score is greater than 700, and there are no restrictions on the name.

- Returns "Manual Review" if the income is greater than 2000, the score is greater than 600, and there are no restrictions on the name.

- Returns "Rejected" otherwise.

3. Given the values in the cells: A1 (Product Price); B1 (Category: "Electronic", "Clothing", "Food"); and C1 (Stock: "Available" or "Sold Out"). Write a formula that:

- Returns "Promotion" if the product is in the "Electronic" category, the price is greater than 1000, and it is available.

- Returns "Discount" if the product is in the "Clothing" category, the price is greater than 100 and it is available.

- Returns "Out of Stock" if the product is sold out.

- Returns "Regular Price" otherwise.

4. Given the values in the cells: A1 (Amount Spent by the Customer); B1 (Purchase Frequency: "High", "Medium", "Low"); and C1 (Customer Time: "New", "Old"). Write a formula that:

- Return "VIP Customer" if the amount spent is greater than 1000, the frequency is "High", and the customer tenure is "Long-standing".

- Return "Premium Customer" if the amount spent is greater than 500, the frequency is "Medium" or "High", and the customer tenure is "Long-standing".

- Return "Regular Customer" otherwise.

5. Given the values in the cells: A1 (Age); B1 (Contribution Time); and C1 (Gender: "Male" or "Female"). Write a formula that:

- Returns "Retirement Approved" if the age is greater than or equal to 65 (men) or 60 (women) and the contribution time is greater than or equal to 35 years.

- Returns "Partial Retirement" if the age is greater than or equal to 60 (men) or 55 (women) and the contribution time is greater than or equal to 30 years.

- Returns "Not Eligible" otherwise.

6. Click here to access the spreadsheet for the next exercises.

a) What is the students’ grade average? Use AVERAGE to calculate.

b) Over the eight class days, which student missed all classes? And which student was present in all of them?

c) The passing average is 5, which students will pass? Indicate in the spreadsheet with the words "Passed" and "Did not pass"

d) Imagine that the teacher will put into recovery the students who missed more than 3 times and had an average below 5. Which students will go into recovery? Indicate in the spreadsheet with the words "Recovery", "No recovery".

e) If the student misses more than 6 times, unless they get a grade above 9, they will go to recovery. Which students fall into this case? Indicate with "Special recovery case" in the spreadsheet.

7. Given a year in cell A1, write a formula using nested IFs to check whether the year is a leap year. If it is a leap year, return "Leap year"; otherwise, return "Not a leap year". (use the MOD function)

8. (Challenge question and unrelated to the class) -> Suppose a person's date of birth is in cell A1. Write an Excel formula that calculates that person's age based on the current date.