Processing Data Through the IF Function

In data analysis, the IF function is commonly used to clean and process the existing data through logical checks on conditions.

Example One: Null Value of a Field

When processing data, you may need to perform calculations only on valid (non-null and non-zero) values while safely ignoring invalid data. In this case, you can use the IF function for logical checks.

Example data: Internet_Access_Statistics.xlsx

For example, if the Statistical date field in the Internet_Access_Statistics table contains null values, you need to process non-null values by adding 10 days to the current date. Since null values would otherwise default to the Unix epoch (1970-01-01), you need to nest the ISNULL function inside the IF function in the formula to ensure correct results.

Create an analysis subject, download and upload the example data Internet_Access_Statistics, click Formula Column, set Name of the Added Formula Column to Ten Days Later, enter the formula IF(ISNULL(Statistical date)=1,null,DATEDELTA(Statistical date,10)), and click OK, as shown in the following figure.

Note:

The DATEDELTA function can return a specific date after a given date.

Example Two: Correct Division Result

When you need to perform division calculations on fields, if either the dividend or the divisor is 0, the IF function can also be used.

For details, see Displaying Infinity/Negative/Empty Values as Zero.

If you want to categorize and assign values to data based on specific conditions, the IF function is an excellent solution.

Usually, you can nest functions like AND, OR, and FIND inside the IF function.

Example One: Multiple IF Functions Nested for Use

Example data: Regional Data Analysis.xlsx

If a value in the Customer column contains Company, the value will be classified as Company. If a value in the column contains College, the value will be classified as Education Industry. Otherwise, the value will be classified as Other.

Create an analysis subject and upload the example data Regional Data Analysis. Click Formula Column, set Name of the Added Formula Column to Customer Classification, enter the formula IF(FIND("Company",Customer)!=0,"Company",IF(FIND("College",Customer)!=0,"Education Industry","Other")), and click OK, as shown in the following figure.

The following table describes the formula in detail.

Example Two: Combination with the Logic Function

When you still use the built-in dataset, the contract will be classified as Designated Contract if the contract amount is more than 500,000 or the province is Zhejiang Province. Otherwise, the contract will be classified as Other.

Enter the formula IF(OR(Contract Amount >500,000,FIND("Zhejiang Province",Province)!=0),"Designated Contract","Other"), as shown in the following figure.

You can also use || to realize the same effect as the OR function in this formula. In this case, the formula isIF(Contract Amount > 500000||FIND("Zhejiang Province",Province)!=0,"Designated Contract","Other").

Example data: Internet_Access_Statistics.xlsx

During data processing, issues with logical value errors in datasets may frequently occur. For example, in a data table where the values of the Total_Stay_Time column contain negative values, which is clearly erroneous data. In view of this, it is necessary to perform data cleansing by flagging all negative Total_Stay_Time values as Error for identification.

Procedure

Create an analysis subject and upload the example data Internet_Access_Statistics. Click Formula Column, set Name of the Added Formula Column to Total Dwell Time Verification, enter the formulaIF(Total_Stay_Time>=0,Total_Stay_Time,"Error"), and click OK, as shown in the following figure.

Note:

1. You need to select the Total_Stay_Time field from the left field selection box.

2. Since Error is a text string, it must be enclosed in double quotation marks.

Effect Display