IF Function: Change the Value to Display Depending on a Condition

Article Number:040505

The IF function determines whether or not a specified condition is met, and returns a different value depending on the result.

IF function syntax

The content has been copied.
IF(condition, value_if_true, value_if_false)

The arguments you need to specify for an IF function are the "condition", "value_if_true", and "value_if_false" arguments.
You can specify an IF function with other functions or formulas nested within it. When a formula is long, you can use line breaks and indentation to make it easier to read.

Operators that can be used in conditions

For the "condition" argument, you can combine field codes or values with a comparison operator.
The following operators can be used to specify conditions.

  • =
  • !=
  • <>
  • <
  • <=
  • >
  • >=

When comparing values of numeric type fields (such as "Number" fields), you can use all of the operators above.
When comparing values of string type fields (such as "Text" fields), you can only use the following operators: "=", "!=", "<>". In other words, you can only determine whether or not one string value is equal to another.

For information on field data types, refer to Data Types of Fields.

Specifying the "value_if_true" and "value_if_false" arguments

For the "value_if_true" and "value_if_false" arguments, you can specify values of the numeric, string, boolean, or error type, or formulas that return one of these value types.
When specifying a character string directly, enclose it in double quotation marks ("").

The content has been copied.
IF(Lodging="Yes", "Travel application required", "Travel application not required")

If you want to display a string value as a calculation result, use a "Text" field for the formula.

Returning empty values

If you want a formula to return an empty value, enter double quotation marks ("") as the "value_if_true" or "value_if_false" argument.

The following example shows a formula that displays a blank field when 0 is entered as the value of the "Quantity" field. If the value of the "Quantity" field is not 0, the formula displays the result of multiplying the "Quantity" and "Unit price" fields.

The content has been copied.
IF(Quantity=0, "", Unit_price\*Quantity)

Specifying as a condition whether or not a field used in a calculation contains a value

To specify as a condition whether or not a field used in a calculation has a value entered in it, specify empty double quotation marks ("") in the formula.
For example, specifying ="" after a "Number" field will make the formula return TRUE if the "Number" field is empty, and FALSE if 0 is entered as the value.

The following example shows a formula that displays "Not entered" when no value is entered in the "Quantity" field. If a value is entered in the "Quantity" field, the result of multiplying the "Quantity" field and "Unit price" field is displayed.

The content has been copied.
IF(Quantity="", "Not entered", Unit_price\*Quantity)

If you want to display a string value as a calculation result, use a "Text" field for the formula.

Combining the IF function with other functions

The IF function can be combined with CONTAINS functions and AND, OR, and NOT functions to set more detailed conditional expressions.
For details, refer to the page for each function.

When a field used in a calculation has no value entered

If a numeric type field without a value entered is referenced by a formula, the field's value is treated as 0. If a string type field without a value entered is referenced by a formula, the field's value is treated as an empty string ("").

Formula examples

When specifying a field in a formula, make sure to specify the field code as the argument (and not the field name).
In the examples below, the field code is set for each field as the field name with underscores instead of spaces (e.g., the field code for the "Total score" field is "Total_score").

Formula to display "Pass" when a total score is 80 or higher

The following formula displays "Pass" if a total score is 80 or higher, and "Fail" otherwise.

The content has been copied.
IF(Total_score>=80,"Pass","Fail")

The formula in this example needs to be set for a "Text" field. Place a "Text" field on the app form, select Calculate automatically in the field settings, then enter the formula.

Formula to display a price discounted by 15% if the total is greater than or equal to a specified amount

The following formula displays a price discounted by 15% if the amount entered in the "Total" field is greater than or equal to 1,000 yen. If the amount is less than 1,000 yen, the price is displayed as is.

The content has been copied.
IF(Total>=1000, Total\*0.85, Total)

Formula to display a different cost depending on whether or not an option is selected

The following formula displays a lodging fee of 9,000 yen if "Yes" is selected for the "Breakfast" field, and a lodging fee of 7,000 yen if "No" is selected.

The content has been copied.
IF(Breakfast="Yes", 9000, 7000)

The "Breakfast" field can be a "Drop-down" field or a "Radio button" field.
When you specify a drop-down or radio button option as part of a condition, you need to enclose the option name in double quotation marks ("").

Formula to perform a different calculation depending on whether the input value is 0 or a number greater than or equal to 1

The following formula calculates a budget ratio based on the "Budget" and "Actual amount" fields.
When 0 is entered in the "Budget" field, the calculation result field is left blank. When a number greater than 0 is entered, the budget ratio is calculated.

The content has been copied.
IF(Budget>0, (Actual_amount/Budget)\*100, "")

If only the budget ratio calculation "Actual_amount/Budget" is specified in a formula, an error will occur when 0 is entered in the "Budget" field because the denominator in the calculation is 0. Using the IF function in the formula allows you to separate cases so that the calculation is not performed if 0 is entered in the "Budget" field.

Formula to determine whether a target value has been achieved

The following formula displays "Achieved" if the value entered in the "Total" field is greater than or equal to the value in the "Target" field, and "Not achieved" otherwise.

The content has been copied.
IF(Total>=Target,"Achieved","Not achieved")

The formula in this example needs to be set for a "Text" field. Place a "Text" field on the app form, select Calculate automatically in the field settings, then enter the formula.

Formula for displaying test results

The following formula displays "Perfect score" for a test score of 100, "Pass" for a test score of 80 or greater, and "Fail" for a test score of less than 80.

The content has been copied.
IF(Score=100,"Perfect score",IF(Score>=80,"Pass","Fail"))

The formula in this example needs to be set for a "Text" field. Place a "Text" field on the app form, select Calculate automatically in the field settings, then enter the formula.