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.

Writing Conditional Expressions

Conditional Expressions: Total>=80

By using comparison operators, you can compare field codes and values that you want to use as conditions.

Branching based on the data of a field (1)
Example: IF(Total>=80,"OK","NG")

"Total>=80" is a conditional expression. Write the expression starting with a field code (Total), followed by the comparison operator (>=) and the value (80).
It displays "OK" if the "Total" field value is more than or equal to 80. Otherwise, it displays "NG".
  • Field code: Write a field code for the field that is referred by the formula.
  • Comparison operators:
    • When comparing numeric type fields (such as Number fields), you can use =, !=, <>, <, >, <=, >= as comparison operators.
    • When you compare string type fields (such as Text field), you can only use =, !=, and <>.
    • For details on the field data types including numeric and string types, refer to "Data Types of Values That Are Referred by Formulas".
      For details on comparison operators, refer to the description on =, !=, <>, <, >, <=, >= in "List of Operators and Functions". 
  • Values: For numeric values, you can write them as they are. For text strings, you need to enclose them with double quotation marks (").
Branching based on the data of a field (2)
Example: IF(CONTAINS(Days_Worked, "Sunday"), "Worked on Sundays", "Not Worked on Sundays")

"CONTAINS(Days_Worked, "Sunday")" is a conditional expression. It displays "Worked on Sundays" if Sunday is selected in the "Days Worked" field. Otherwise, "Not Worked on Sundays" is displayed.

If you want to refer to check boxes and multi-choices in a conditional expression with IF function, use CONTAINS function.
When you write "equal to" in a conditional expression, "=" is used in the case of "Branching based on the data of a field (1)". However, for the check boxes and multi-choices, you need to use "," instead of "=".
For details on CONTAINS function, refer to the following pages:

Branching by Specifying Multiple Fields
Example: IF(AND(Academic_subject>=80,Practical_skill>=80),"OK","NG")

"AND(Academic_subject>=80,Practical_skill>=80)" is a conditional expression. It displays "OK" if the values of both "Academic subject" and "Practical skill" fields are more than or equal to 80. Otherwise, it displays "NG".

In the IF function, you can write conditional expressions with multiple fields.
By using AND function and OR function, you can concatenate conditional expressions with ",".
Available comparison operators are the same as the ones that are explained in the "Branching based on the data of a field (1)" section.

For details on AND function and OR function, refer to the "AND, OR, NOT Functions: Combining Conditions" page.

Examples of Formulas Using Conditional Expressions

This section shows you some examples of formulas using conditional expressions.

Formula for invoice price calculation: IF(Total_Price>=1000, Total_Price*0.85, Total_Price)

Displays 15 % discounted price when the total price is more than or equals to 1,000 yen. Otherwise, displays the total price as is.
Formula for 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 for 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 for 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 for 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: How Empty Fields Are Handled

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.

Example: Changing the Invoice Price Based on the Total Amount

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 Price" as the field name and "Total_Price" as 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 "WSP".
    Keep the setting screen open.

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

    • Formula: IF(Total_Price>=100000, Total_Price*0.85, Total_Price)
      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 price is more than or equals to 100,000 yen.

Example: Changing the Value to Display Based on the Selected Radio Button

In a conditional expression in IF function, you can refer to drop-down and radio button fields.
You can also change the value to display based on which radio button is selected. For example, you can display 9,000 yen when "with" is selected, and 7,000 yen when "without" is selected.

  • Formula example: IF(Breakfast="with", 9000, 7000)