IF function
Condition
The condition to check.
ThenValue
The value to return if the condition is TRUE.
OtherCondition
An additional condition to check.
OtherThenValue
An additional value to return if the preceding condition is TRUE.
OtherwiseValue
The value to return if no conditions are TRUE. If omitted, a blank value is used.
Returns
The value associated with the first condition which is TRUE (evaluated
from left to right). If no condition is TRUE, the
otherwiseValue parameter is returned. If there is no such
parameter, a blank value is returned.
Returns one of the given values based on logical conditions. IF(Field1 > 20, 2, Field2 > 40, 4, 6)IF(Field1 > 20; 2; Field2 > 40; 4; 6) returns 2 if Field1 has a value greater than 20, 4 if Field1 does not have a value greater than 20 and Field2 has a value greater than 40, and 6 otherwise.
Multiple conditions
IF supports multiple conditions. Consider this formula:
The preceding formula returns 4 if the value of Field1 is greater than 20. Otherwise, it returns 2 if the value of Field1 is less than 10. If the value of Field1 is not greater than 20, and it is not less than 10, -1 is returned.
Spreadsheets often require you to use the IFS function to use multiple conditions. Calcapp supports multiple conditions with both IF and IFS.
Logical "and" (conjunction)
Use logical functions and logical operators to express more complex relationships. This formula returns "Success!" only if Field1.ValueField1,Value is greater than 20 and Field2.ValueField2,Value is less than 10:
The AND function is more familiar to spreadsheet users — the && operator is a Calcapp extension — and produces the same result:
Logical "or" (disjunction)
This formula returns "Success!" if Field1.ValueField1,Value is greater than 20 or Field2.ValueField2,Value is less than 10:
The OR function is, again, more familiar to spreadsheet users than Calcapp's || operator and performs identically:
Logical "not" (negation)
To negate a logical value (turning FALSE into TRUE and vice versa), use the ! Calcapp operator or the NOT function.
These formulas both return 2 only if the value of Field1 is not odd:
Blank values
If a third parameter is not passed to IF, a blank value is returned if the condition does not evaluate to TRUE. Both these formulas return a blank value if the value of Field1 is not 10:
If a user has not filled out a field, and it has no initial value, it is considered blank. To determine if a value is blank, use the ISBLANK function.
This formula returns "No value" if Field1 has a blank value, and the value of Field1 otherwise:
The specialized IFBLANK function may be used to write an equivalent, shorter formula:
ISDEFINED does the reverse. This formula returns "Filled out!" only if a value is associated with Field1:
Accessing properties
These formulas are normally equivalent:
Writing Field1Field1 in a formula normally accesses its Value property if you don't write it explicitly by writing .Value,Value after the field name.
Access other properties by writing the property name after the name, with ., appearing between the two:
The formula above returns "Valid!" only if Field1 is valid and "Invalid!" otherwise.
This formula returns 4 only if Button1 is visible and the user has entered a value in Field1, otherwise a blank value is returned:
There are hundreds of properties, which can be accessed through formulas and set using formulas. These include properties for colors, the recipients of reports and whether buttons should be grayed out. Refer to the property documentation for more information.
Arrays and IF
When initializing arrays, use IF to determine if an element should be part of the array.
This formula returns the array { 2, 4, 6, 8 }{ 2; 4; 6; 8 } only if Field1 has a value that is greater than 20, and returns the array { 2, 4, 8 }{ 2; 4; 8 } otherwise:
IF alternative: SWITCH
Consider using SWITCH instead of this function if you need to test for equality against the same value. These formulas are equivalent:
IF alternative: CHOOSE
CHOOSE selects which parameter to return based on the first parameter. If the first parameter is 3, the third parameter is returned. If it is 20, the 20th parameter is returned.
These formulas are equivalent:
IF in action formulas
IF can be used in action formulas to determine what action to take. Action formulas are run in response to an event being triggered, such as a button being pressed.
This formula resets Field1 to its initial value if the value of Field1 is greater than 20. Otherwise, it assigns the value 5 to the same field.
Functions like RESET and the := operator can only be used in action formulas.
Examples
Returns 2.
Returns 4.
Returns 2 only if Field1 has a value greater than 20. Otherwise, 4 is returned.
Returns 4 if Field1.ValueField1,Value is greater than 20, 2 if the same value is less than 10 and -1 otherwise.
Returns 4 if Field1.ValueField1,Value is greater than 20, 2 if the same value is less than 10 and -1 otherwise. This formula is equivalent to IF(Field1 > 20, 4, IF(Field1 < 10, 2, -1))IF(Field1 > 20; 4; IF(Field1 < 10; 2; -1)), but does not use nested IF functions, making the formula easier to write and maintain.
Returns 2 only if Field1 has a value greater than 20 and said value is odd. Otherwise, 4 is returned. That means that 21 and 23 qualify, but 22 and 11 do not.
Returns 2 only if Field1 has a value greater than 20
and said value is odd. Otherwise, 4 is returned. This
formula returns the exact same result as IF((Field1 > 20) &&
ISODD(Field1),
2, 4)IF((Field1
> 20) &&
ISODD(Field1);
2; 4), with the only difference being the use of the AND function, instead of
the && operator. AND is a standard
spreadsheet function, whereas the && operator is a
Calcapp extension.
Returns 2 only if Field1 has a value greater than 20 or said value is odd. Otherwise, 4 is returned. That means that 21, 22, 23, 1, 3 and 5 qualify, but 2, 4 and 6 do not.
Returns 2 only if Field1 has a value greater than 20 or said value is odd. Otherwise, 4 is returned. This formula returns the exact same result as IF((Field1 > 20) || ISODD(Field1), 2, 4)IF((Field1 > 20) || ISODD(Field1); 2; 4), with the only difference being the use of the OR function, instead of the && operator. OR is a standard spreadsheet function, whereas the || operator is a Calcapp extension.
Returns "Y" only if the values of Field1 and Field2 are not even. The ! operator can also be expressed using the standard spreadsheet function NOT.
Returns the array { 2, 4, 6, 8 }{ 2; 4; 6; 8 } only if Field1 has a value that is greater than 20. Otherwise, the array { 2, 4, 8 }{ 2; 4; 8 } is returned. In other words, IF inside of an array can be used to determine if an array element should be part of the array by leaving out the third parameter to IF.
Returns the array { 2, 4, 6, 8 }{ 2; 4; 6; 8 } only if Field1 has a value that is greater than 20. Otherwise, the array { 2, 4, BLANK(), 8 }{ 2; 4; BLANK(); 8 } is returned. This example illustrates that leaving out the third parameter to IF results in the element being omitted entirely from the array, whereas using BLANK()BLANK() as the third parameter instead includes a blank value.
Returns the array { 2, 4, 6, 8 }{ 2; 4; 6; 8 } only if Field1 has a value that is greater than 20. Otherwise, the array { 2, 4, 8 }{ 2; 4; 8 } is returned. This example uses the | operator, which joins arrays together. It produces the same result as { 2, 4, IF(Field1 > 20, 6), 8 }{ 2; 4; IF(Field1 > 20; 6); 8 }.
Returns { 4, 4 }{ 4; 4 } if the value of Field1 is greater than 20 and Button1 is enabled (not grayed out), { 4, 2 }{ 4; 2 } if the value of Field1 is greater than 20 and Button1 is disabled, { 2, 4 }{ 2; 4 } if the value of Field1 is not greater than 20 and Button1 is enabled, and finally { 2, 2 }{ 2; 2 } if the value of Field1 is not greater than 20 and Button1 is disabled. When an array is used for the condition, the IF function is run once per array element and the results collected in an array. In other words, this formula is equivalent to { IF(Field1 > 20, 4, 2), IF(Button1.Enabled, 4, 2) }{ IF(Field1 > 20; 4; 2); IF(Button1,Enabled; 4; 2) }, but is shorter and works with variable-length arrays.
Returns an array with 100 number elements (provided that there are 98
number fields between NumberField1 and NumberField100).
If the value of NumberField1 is greater than 10, then the first
element of the returned array is 4, otherwise it is 2, and so on for the
other fields. : specifies a range and is a
short-hand for expressing arrays. If Field1 appears before
Field2, which appears before Field3, the formulas
{ Field1, Field2,
Field3 }{ Field1; Field2; Field3 }
and Field1:Field3Field1:Field3 are equivalent.