Version
| FineBI Version | Functional Change |
|---|---|
6.0 | / |
6.0.16 | Enhanced the multi-condition value assignment capability of the IF function. |
Overview
| Grammar | IF(Condition expression 1,Result1,Condition expression 2,Result2,.....,Other results) If condition expression 1 is met, result 1 is returned. If condition expression 2 is met and condition expression 1 is not met, result 2 is returned. If no condition expression is met, other results are returned. | |
|---|---|---|
| Parameter 1 | Condition expression | The output result must be of the boolean or number type. |
| Parameter 2 | Result parameter | All result parameters must be of the same type. |
Notes
Among the three parameters in the IF function, the first one is of the boolean type (true or false) or the number type (0 or 1); and the second and third ones are of the same type. If one of the second and third parameters is empty, the result of the non-empty parameter type is returned. If the two parameters are of different types, the value of the text type is returned.
When you add calculation fields into a formula, the fields used in the formula cannot be filtered in detail. This is because the indicators after detail filtering supports only aggregation functions. If you need to use the IF function, the fields used cannot be filtered in detail (but can be filtered through other filtering methods).
Example
Example 1: Single-Condition Judgment
Use Score.xlsx as sample data.
(1) Check whether students' scores are qualified. Click Formula Column in the subject and enter the formula, as shown in the following image.
If the score is larger than and equal to 60, Passed is returned. Otherwise, Failed is returned.
(2) The following figure shows the calculation result.
Example 2: Multi-Condition Judgment
If you want to further divide students' scores into more specific ranges (excellent, good, passed, and failed), how can you achieve it using the IF function?
Score < 60: Failed
60≤ Score＜70: Passed
70≤ Score <80: Good
Score ≥ 80: Excellent
Note:(1) Use sample data, click Formula Column in the subject, and enter the formula, as shown in the following image.
If the score meets the first condition (< 60), Failed is returned. The score meeting the first condition will no longer be judged based on the left conditions. The score meeting both the first and second conditions will no longer be judged based on the left conditions. The rest can be deducted.
For FineBI of versions earlier than 6.0.16, the nested IF function is required in multi-condition judgment.
The formula in this example is if(Score<60,"Failed",if(Score<70,"Passed",if(Score<80,"Good","Excellent"))).
(2) The following figure shows the calculation effect.
More Common Examples
| Scenario Example | Formula | Description |
|---|---|---|
Check whether data is null. | IF(isnull(Order Number),0,1) | If the order number is null, 0 is returned. Otherwise, 1 is returned. |
IF + AND condition | IF(AND(Condition 1,Condition 2),"Yes","No") | If both condition 1 and condition 2 are meet, 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. |
