# IF function

**IF**(Condition, ThenValue, OtherCondition..., OtherThenValue..., OtherwiseValue?)

**IF**(Condition; ThenValue; OtherCondition...; OtherThenValue...; OtherwiseValue?)

## 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.