Overview
Application Scenario
The IF function is a commonly used logical function for data processing and analysis. You can nest other functions inside the function to check whether one or more conditions are met, thereby expanding the application scenarios of the IF function. Functions such as AND, OR, and IN are often nested inside the IF function for use.
Function Description
Scenario Example | Formula | Description |
IF + AND Condition | IF(AND(Condition 1,Condition 2),"Yes","No") | If both Condition 1 and Condition 2 are met, Yes is returned. Otherwise, No is returned. |
IF + OR Condition | IF(OR(Condition 1,Condition 2),"Yes","No") | If either Condition 1 or Condition 2 is met, Yes is returned. Otherwise, No is returned. |
IF + IN Condition | IF(IN(Expression, Parameter 1, Parameter 2, Parameter 3, ...),"Yes","No") | If the result of the expression belongs to the set constructed by Parameter 1, Parameter 2, Parameter 3, ..., Yes is returned; otherwise, No is returned. |
Example
Example 1: IF_AND Judgment
For details, see Determining Whether a Date Field Is Within a Specified Period.
Example 2: IF_OR Judgment
Sample data:Contract Information.xlsx
1. Create an analysis subject and choose Local Excel > Upload to upload the sample data Contract Information. Use the table to create a component and select Add Calculation Field for the service type.
Either the condition that Contract Amount is larger than 500,000 or Payback Amount is larger than 100,000 is met, Contract Type is Long-Term Contract. Otherwise, it is a Purchase Contract. Enter IF(OR(Contract Amount > 500000, Payback Amount > 100000), "Long-Term Contract", "Purchase Contract"), and click OK, as shown in the following figure.
2. Add fields in the component, and you can see that if Contract Amount is larger than 500,000 or Payback Amount is larger than 100,000, Contract Type is Long-Term Contract. Otherwise, it is Purchase Contract, as shown in the following figure.
Example 3: IF_IN Judgment
Sample data:User Data.xlsx
1. Create an analysis subject and choose Local Excel > Upload to upload the sample data User Data. Use the table to create a component and select Add Calculation Field for the service type.
If Cooperation Status belongs to Follow-up, BI Cooperation, and Cooperation, Service Type is Service. Otherwise, it is No Service. Enter IF(IN(Cooperation Status,"Follow-up","BI Cooperation","Cooperation"),"Service","No Service"), and click OK, as shown in the following figure.
2. Add fields in the component. You can see that if Cooperation Status belongs to Follow-up, BI Cooperation, and Cooperation, Service Type is Service. Otherwise, it is No Service, as shown in the following figure.
