Using the IF Function for Data Processing

  • Last update:  2022-04-26
  • 1. Overview

    In data analysis, users often use IF judgment conditions to clean and process the existing data.

    2. Handling special values

    2.1 Example 1: the field value is empty

    When processing data, you may encounter the situation that the field is empty. At this time, if you need to calculate or process the formula of non-0 data on this basis, you need to only process the non-empty data. You can use the IF function to judge.

    Sample data:

    404.Internet Access Statistics.xlsx

    For example, in the "Internet Access Statistics", there is empty data in the "Statistical date" in the access statistics table. It is necessary to process the non empty "Statistical date" data as ten days after the current time. Since the null value will be processed as year 1970, it is necessary to nest a function combining IF and ISNULL outside the formula to ensure the correctness of the processed results.

    Create a new self-service dataset, create a "new column" and use the formula  IF(ISNULL(Statistical date)=1,null,DATEDELTA(Statistical date,10)), as shown in the following figure:

    Note: the DATEDELTA function can return the specific date after the specified date.

    2.2 Example 2: ensure the correctness of division results

    If the field needs to be divided, and the dividend or divisor is 0, you can also use the IF function.

    For more details, see file: "Infinity / negative number / null value is displayed as 0".

    2. Condition assignment

    Users need to classify and assign data according to some conditions. At this time, IF function is a better choice.

    Usually, IF function is nested with AND, OR, FIND, etc.

    2.1 Example 1: nested use of multiple IF

    Use the built-in dataset "FRDemo_ProfitAnalysis".

    If the "Item" column contains the "income" field, it is equivalent to "Income", if contains the "expense" field, it is equivalent to "Expense", otherwise it is "Other".

    Create a self-service dataset, create a new column and enter the formula: IF(FIND("income",${Item})!=0,"Income",IF(FIND("expense",${Item})!=0,"Expense","Other")), as shown in the figure below:

    Note: fields need to be selected from the left side of the formula box and cannot be entered manually.

    2021-08-16_15-53-44.png

    Formula description:

    FormulaDescription
    FIND("income",${Item})!=0If "income" is found in the "Item" field, the output is true, otherwise it is false.
    IF(FIND("income",${Item})!=0,"Income",IF(FIND("expense",${Item})!=0,"Expense","Other")) If the "Item" column contains the "income" field, it is equivalent to "Income", if the "expense" field is equivalent to "Expense", otherwise it is "Other".

    2.2 Example 2: use with logical functions

    The built-in data set is still used. If one of "Current period > 1000" and "Category" belongs to "Expenditure" is satisfied, it is assigned as "specified contract", otherwise it is other.

    Input formula:

    IF(OR(${Current period}>1000,FIND("Expenditure",${Category})!=0),"Designated expense","Others") , as shown in the figure below:

    2021-08-16_16-20-45.png

    Formula description:

    FormulaDescription
    FIND("Expenditure",${Category})!=0)If the "Category" is "Expenditure", return "TRUE"; otherwise, return "FALSE".
    OR(${Current period}>1000,FIND("Expenditure",${Category})!=0)Return "TRUE" if one of the two conditions is met

    IF(${Current period}>1000||FIND("Expenditure",${Category})!=0,"Designated expense","Others")

    3. Processing logic error values

    During data processing, users often encounter the problem of "logical value error" of data. For example, there is a data table with negative total dwell time. It is obvious that these data are wrong. At this time, data processing needs to be carried out to change those with negative total residencel time to "Error" for identification.

    2021-08-16_16-50-32.png

    3.1 Operation steps

    Create a new self-service dataset, create a "New column" and use the formula:

    IF(${Total residence time}>=0,${Total residence time},"Error"), as shown in the following figure:

    Formula
    Description
    IF(${Total residence time}>=0,${Total residence time},"Error")IF "Total residence time">=0, Then output according to the original content, otherwise the value is "Error"

    Note 1: the "Total residence time" field needs to be added into the formula box by clicking from the field selection box on the left.

    Note 2: since "Error" is text data, double quotation marks are required.

    2021-08-16_16-56-11.png

    3.2 View the effect

    2021-08-16_16-56-31.png

    附件列表


    主题: Advanced Data Analysis
    • Helpful
    • Not helpful
    • Only read

    滑鼠選中內容,快速回饋問題

    滑鼠選中存在疑惑的內容,即可快速回饋問題,我們將會跟進處理。

    不再提示

    10s後關閉

    Get
    Help
    Online Support
    Professional technical support is provided to quickly help you solve problems.
    Online support is available from 9:00-12:00 and 13:30-17:30 on weekdays.
    Page Feedback
    You can provide suggestions and feedback for the current web page.
    Pre-Sales Consultation
    Business Consultation
    Business: international@fanruan.com
    Support: support@fanruan.com
    Page Feedback
    *Problem Type
    Cannot be empty
    Problem Description
    0/1000
    Cannot be empty

    Submitted successfully

    Network busy