Зміст курсу
Google Spreadsheets
Google Spreadsheets
IF and Nested IF Functions
We will discuss the topic of IF and Nested IF Functions. The IF function allows you to perform logical tests and return different results depending on the condition. It is a fundamental function for working with conditional operations. Nested IF enables combining multiple conditions in a single formula for more complex evaluations.
IF Function
The IF function checks a condition and returns one value if the condition is true and another value if it is false.
Syntax:
IF(logical_expression, value_if_true, value_if_false)
- logical_expression - An expression or reference to a cell containing an expression that represents a logical value, i.e., TRUE or FALSE;
- value_if_true - The value returned by the function if logical_expression is TRUE;
- value_if_false - OPTIONAL−blankbydefaultOPTIONAL - blank by default - The value returned by the function if logical_expression is FALSE.
Nested IF Function
Nested IF allows you to evaluate multiple conditions sequentially. Each subsequent condition is checked if the previous one is false. This is useful when decisions need to be made based on several conditions.
Syntax:
IF(condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false2))
- condition1 - An expression or reference to a cell that evaluates the first condition being checked;
- value_if_true1 - The value returned by the function if condition1 is TRUE;
- condition2 - An expression or reference to a cell that evaluates the second condition if condition1 is FALSE;
- value_if_true2 - The value returned by the function if condition2 is TRUE;
- value_if_false2 - The value returned by the function if condition1 and condition2 are FALSE.
Examples for IF and Nested IF
| Value (A) | Formula | Result | Comment | |
|---|---|---|---|---|
| 50 | IF(A2 > 50, A2 * 2, A2 / 2) | 25 | If A2 (50) is greater than 50, returns double the value of A2; otherwise, returns half of A2. | |
| 5 | IF(A4 < 0, A4 * 10, A4 + 10) | -20 | If A4 (-2) is less than 0, returns A4 multiplied by 10; otherwise, returns A4 plus 10. | |
| -2 | IF(A5 > 100, "Too Much", "Acceptable") | Acceptable | If A5 (80) is greater than 100, returns "Too Much"; otherwise, "Acceptable". | |
| 80 | IF(A6 = "", "Empty", "Not Empty") | Empty | If A6 is empty, returns "Empty"; otherwise, "Not Empty". | |
| IF(A2 = "", "Empty", A3 * 2) | 10 | If A2 is empty, returns "Empty"; otherwise, doubles the value of A3. | ||
| IF(AND(A3 >= 10, A3 <= 20), "In Range", "Out of Range") | Out of Range | If A3 (5) is within the range of 10 to 20, returns "In Range"; otherwise, "Out of Range". | ||
| IF(OR(A3 < 0, A5 > 100), "Invalid", "Valid") | Valid | If A3 (5) is less than 0 or A5 (80) is greater than 100, returns "Invalid"; otherwise, "Valid". | ||
| IF(AND(A3 < 10, A5 < 10), "Both Less Than 10", "One or Both Greater Than 10") | One or Both Greater Than 10 | If A3 (5) and A5 (80) are less than 10, returns "Both Less Than 10"; otherwise, "One or Both Greater Than 10". | ||
| IF(AND(A5 >= 50, OR(A3 < 10, A4 > 0)), "Conditions Met", "Conditions Not Met") | Conditions Met | If A5 (80) is greater than or equal to 50 and (A3 (5) is less than 10 or A4 (25) is greater than 0), returns "Conditions Met". | ||
| IF(A2 < 0, "Negative", IF(A2 = 0, "Zero", "Positive")) | Positive | If A2 (5) is less than 0, returns "Negative"; if 0, "Zero"; otherwise, "Positive". | ||
| IF(A5 * 2 < 100, "Less Than 100", IF(A5 * 3 > 200, "Greater Than 200", "Between 100 and 200")) | Greater Than 200 | If the double value of A5 (80) is less than 100, returns "Less Than 100"; if the triple value is greater than 200, returns "Greater Than 200"; otherwise, "Between 100 and 200". |
Дякуємо за ваш відгук!
