Version
| FineBI Version | Functional Change |
|---|---|
6.0.16 | / |
Overview
The IFERROR function is used to identify and handle exceptional values.
Grammar | IFERROR(value1,value2) Determines whether the value1 is an exceptional value. If so, the value2 is returned. If not, the value1 is returned. | |
Parameter 1 | value1 | Any expression to be determined whether is an exceptional value or not. Exceptional value (excluding the null value) includes the positive infinity (∞), negative infinity (-∞), and NAN value (namely the result obtained from the calculation between any value and the infinity). |
Parameter 2 | value2 | Field with the same type as value1. |
Example
You can download the example data IFERROR.xlsx.
Since two defective machines did not record data successfully, exceptional values occur in the machine temperature table when you calculate the month-on-month (hereinafter referred to as MOM) growth rate.
1. Add a column in the analysis subject to calculate the MOM growth rate with the formula 1 - Yesterday's Temperature / Today's Temperature, through which you can view the temperature change trend.
The machine J016 does not record data in the Today's Temperature column, resulting in the MOM growth rate as the negative infinity (-∞), which is troublesome for later calculations. Therefore, you need to handle exceptional values.
2. Add a column named Exceptional Value Handling and enter the formula shown in the following figure.
If the MOM growth rate is an exceptional value, 0 is returned. If not, the MOM growth rate is returned.
3. The following figure shows the calculation result.
