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

Example of conditional expression

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. In this example, the expression is written in the order of a field code (Total), comparison operators (>=) , and the value (80). You can also specify a field code instead of the value.
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 of the field referenced 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 <>. You can compare string type fields to check if a string is equal to another string or not. However, you cannot compare them to check which value is bigger or smaller as they are not numeric type fields.
    • 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 (").

For comparison operators, you can specify field codes and values (such as numeric values and text strings), as well as formulas and functions on both sides. For example, you can specify formulas and functions such as (Numeric_Value_A*0.1) and (SUM(Numeric_Value_A, Numeric_Value_B)), on both sides of comparison operators.

Branching based on the data of a field (2)
Example: IF(CONTAINS(Workday, "Sunday"), "Working on Sunday", "Not Working on Sunday")

"CONTAINS(Workday, "Sunday")" is a conditional expression. It displays "Working on Sunday" if Sunday is selected in the "Workday" field. Otherwise, "Not Working on Sunday" 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(Subject>=80,Practical_Skill>=80),"OK","NG")

"AND(Subject>=80,Practical_Skill>=80)" is a conditional expression. It displays "OK" if the values of both "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 goal achievement: IF(Total>=Target_Value,"Achieved","Not Achieved")

Displays "Achieved" if the total is equal to or greater than the target value. Otherwise, displays "Not Achieved". Both "Total" and "Target_Value" are field codes. Since the result will be returned in text, you must set the formula in the Text field.
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 created 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 amount 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 "Included" is selected, and 7,000 yen when "Not Included" is selected for the Breakfast option.

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