Logical
Picking a result from several tests
Definition
Choosing a label from several tests in order means giving IFS each test followed by the result for that test.
Syntax
=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2)
Arguments
| Argument | Meaning |
|---|---|
logical_test1 |
The first test, for example B2>=80. |
value_if_true1 |
The result when the first test passes, for example "high". |
logical_test2 |
The next test, used only when the first test fails, for example B2>=0. |
value_if_true2 |
The result when the second test passes, for example "low". |
Worked example
An exam list stores marks in B2:B4. A mark of 80 or more is high; any other mark of 0 or more is low.
| A: Paper | B: Mark |
|---|---|
| A | 90 |
| B | 12 |
| C | 80 |
Formula: =IFS(B2>=80,"high",B2>=0,"low")
Result: high. 90 is at least 80, so the first test wins and the second test is not used.
Practice
Cell A2 holds 40. In B2, write a formula that shows pass when A2 is at least 50 and fail when A2 is at least 0.
- In B2, write an IFS formula that shows pass when A2 is at least 50, and fail when A2 is at least 0.
- B2 should show the result for 40.
Related functions
IFS is the function that walks the tests in order and returns the matching label.
Common mistake
Putting the wide test first, as in =IFS(B2>=0,"low",B2>=80,"high"): 90 also passes the first test, so the result is low.