IF- FineBI Document

Last update： 2024-01-23

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.

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.

图1.png

(2) The following figure shows the calculation result.

图2.png

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:

The number of conditions cannot exceed 200 for multi-condition judgment.

(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.

图3.png

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.

图4.png

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.

Previous：Overview of common functions

Next：Overview of mathematics and trigonometric functions

Helpful
Not helpful
Only read

中文（简体）中文（繁體）日本語

English

IF