I. Intro
The IF and IFS functions can be used to evaluate and compare values with expected values, and return the required values. They are commonly used in data analysis and comparison. For example, different levels can be set based on the sales volume using the IF function by marking volumes higher than the specified value as "high" and volumes lower than the specified volume as "low".
Arguments:
IF(logical_expression, value_if_true, [value_if_false])
- logical_expression: The conditions to be evaluated.
- value_if_true: The value to return if the condition is evaluated to be true.
- value_if_false: The value to return if the condition is evaluated to be false.
IFS(condition1, value1, [condition2, ...], [value2, ...])
- Condition1: The first condition to be evaluated.
- Value1: The value to return if the first condition is evaluated to be true.
- Condition2 (optional): The second condition to be evaluated.
- Value2 (optional): The value to return if the second condition is evaluated to be true.
II. Steps
- Open the base and click the + icon on the right side to create a new field. Enter a field title, and select Formula as the field type.
250px|700px|reset
- In the formula editor, enter the IF function, select the field, and enter the arguments as needed.
- For example, to evaluate the expenditure of different departments, you can use the following formula: IF([Budget]-[Cost]<0, "over budget", "under budget"). As shown in the example below, the engineering, safety and quality assurance departments have costs above their budget, so they're labeled "over budget".
250px|700px|reset
250px|700px|reset
- In the formula editor, enter the IFS function, select the field, and enter the arguments as needed.
- For example, to determine sales commissions, you can use the following formula: IFS([Sales Amount]>=800,[Sales Amount]*0.2,[Sales Amount]>=500,[Sales Amount]*0.1,[Sales Amount]>=0,[Sales Amount]*0). As shown in the example below, when sales amount exceeds $800, the commission is 20%; when the sales amount exceeds $500, the commission is 10%; and no commission is given for any amount lower than $500.
250px|700px|reset
250px|700px|reset
III. Use cases
Check task completion
Scenario: Determine whether a task has been completed based on its status.
Formula: IF([Task progress]="Completed","✅","❌")
250px|700px|reset
Check whether tasks are overdue
Scenario: Determine if a task is overdue based on the deadline.
Formula: IF(TODAY()>[Deadline],"Overdue","Not")
250px|700px|reset
Check whether cells are empty
Scenario: Determine if there is any missing product information (whether a cell in the field is empty).
Formula: IF([Task note]="","❗️Information missing","✅")
250px|700px|reset
Evaluate grade levels
Scenario: Determine which grade students in the class will receive.
Formula: IFS([Grade]=100,"A", [Grade] >=85,"B", [Grade] >=75,"C", [Grade] >=60,"D",TRUE,"F")
250px|700px|reset
Calculate sales commission
Scenario: Commission is calculated based on the performance of the salesperson. When the sales sum is more than 20,000, the commission is 50%, when the sales sum is between 15,000 and 20,000, the commission is 30%, and when the sales sum is below 15,000, the commission is 20%.
Formula: IFS([Performance]>=20000,"50%", [Performance] >=15000,"30%",TRUE,"20%")
250px|700px|reset
IV. FAQs