Unit 20: Logical Functions

Understanding Logical Functions:

Logical functions provide decision-making tools for information in a spreadsheet. They allow you to look at the contents of a cell, or to perform a calculation, and then test that result against a required figure or value. You can then use the IF logical function to determine which calculation to perform or action to take depending on the outcome of the test.

The IF Function

The IF function is the key logical function used for decision making. It takes the format:

=IF(condition, true, false)

For example, you could use the following formula:

=IF(B2 > 400, “High”, “Low”) where, B2 > 400 is the condition being tested

(this could be translated as “Is the value in cell B2 greater than 400?”)

“High” is the text to display if B2 is greater than 400 (the result of the test is yes or TRUE)

“Low” is the text to display if B2 is less than or equal to 400 (the result of the test is no or FALSE)

If Function – 1

If Function – 2

If Function – 3

If Function – 4

If Function – 5

If Function – 6

---

Nested If Function – 1

Nested If Function – 2

---

If And Function – 1

If And Function – 2

If And Function – 3

---

If Or Function – 1

 

If Tricky

 

The AND Function

The AND function is used to compare more than one condition. It returns TRUE only if all of the conditions are met, and takes the format:

=AND(condition1, condition2,…)

For example, you could use the following formula:

=AND(B2 > 400, C2 < 300) where,

B2 > 400 is the first condition being tested C2 < 300 is the second condition being tested

This will only return the result TRUE if the value in cell B2 is greater than 400 and the value in cell C2 is less than 300. In all other situations, the result will be FALSE.

UNIT 20 – Logical Functions

---