IF Function: Changing the Value to Display Depending on the Condition

You can use the IF function in formulas. IF function is a function that changes the value to display depending on the specified conditional expression.

IF(Conditional expression,When TRUE,When FALSE)

If the conditional expression is satisfied, returns TRUE. Otherwise, returns FALSE.

You can use following operators in your conditional expressions.
=, !=, <>, <, >, <=, >=

  • You can use all operators when comparing numerical types.
  • You can use only =, !=, <> when comparing string types.
    Text, Drop-down, Radio Button, and Lookup (when the source field is string type) fields are string type fields.

For details, see the list of operators and functions.

Example Formula

This section describes example formulas that can be used for the Calculated field or the Text field.
Field codes are specified as conditions in these formulas.
You can also wrap lines or insert line feeds to make long formulas easy to read.

Formula (Invoice price calculation): IF(Total amount>=1000, Total amount*0.85, Total amount)

Displays 15 % discounted price when the total amount is more than or equals to 1,000 yen. Otherwise, displays the total amount as is.
Formula (Accommodation expense calculation): IF(Breakfast="Included", 9000, 7000)

Displays 9,000 yen when breakfast is included. Otherwise, displays 7,000 yen.
Whether to include breakfast fee is determined by the selection status of the "Included" option in the Drop-down or Radio button field. When you specify a drop-down or a radio button option as a condition, you must enclose the option name with double quotation marks("").
Formula (Budget ratio calculation): IF(Budget>0, (Actual amount/Budget)*100, "")

When the budget is '0', budget ratio becomes empty. When the budget is not '0', calculates the budget ratio. Since a denominator might be '0', IF function should be used to classify cases.
Formula (Rating): IF(Score>=80, "Pass", "Fail")

Displays "Pass" when the score is more than or equals to 80. Otherwise, displays "Fail". Since the result will be returned in text, you must set the formula in the Text field.
Formula (Rating): IF(Score=100,"Perfect score",IF(Score>=80,"Pass","Fail"))

IF function can be nested. Displays "Perfect score" when the score is 100, and "Pass" when the score is more than or equals to 80. Otherwise, displays "Fail". Since the result will be returned in text, you must set the formula in the Text field.

TIPS: Empty Fields

Even when there is an empty field, that field value is considered as 0 or "", and the formula is calculated.

  • If you enter "Calculated field = 0", the result becomes TRUE even when the Calculated field is empty, not only when the Calculated field value is '0'.

  • If you specify "Calculated field = """, decision will be made whether the field is empty or not. Returns FALSE when '0' is entered, and TRUE when the field is empty.

  • Described below is the way to make the field empty instead of displaying '0'.
    Specify "" as the result.

    • Example: IF(Quantity=0, "", Unit Price*Quantity)
      When the quantity is 0, the result will be empty. When the quantity is not 0, the result value of multiplication will be displayed.

Procedure

This section describes the way to set a formula which changes the invoice price based on the total amount, by using an example of an order management app.

Image of the completed app:

  1. On the screen to set forms, place a field (Calculated field) to show the total amount.
    Enter "Total amount" as the field name and the field code.
    For details on how to set this field, see the "SUM Function: Sum Calculation" page.

  2. From the parts list shown in the left side, drag and drop the Calculated field.

  3. Open the field setting screen, and change the field name to "Invoice Price".
    Keep the setting screen open.

  4. Enter a formula that displays 15 % discounted price when the total amount is more than or equals to 100,000 yen, and displays the total amount as is when the total amount is less than 100,000 yen.

    • Formula: IF(Total amount>=100000, Total amount*0.85, Total amount)
      Then, select Number (Example: 1,000).
  5. Click Save on the calculation setting screen.

  6. Click Save Form on the upper left side of the screen, and then click Update App on the upper right side of the screen.

Now, the 15 % discounted invoice price is displayed when the total amount is more than or equals to 100,000 yen.

Example of Drop-Down/Radio Button Setting

In a formula, you can refer to drop-down and radio button fields. You can use them as conditions in the IF function.
You can change the amount to display, based on the option selected. For example, you can display 9,000 yen when the "Included" option is selected, and 7,000 yen when the "Not included" option is selected.

  • Example of radio button options: "Included", "Not included"

  • Example formula: IF(Breakfast="Included", 9000, 7000)