The IF formula function allows you to make logical comparisons when calculating values. You can use multiple criteria, but we’ll first show how to use a single criterion.

## Using a single criterion

Let’s say you have two fields, *Field1* and *Field2*,
where *Field1* is an input field where users enter values and
*Field2* is an output field which calculates values. To make
*Field2* multiply the *Field1* value by 2 if the
*Field1* value is less than 10 and multiply it by 4 otherwise,
use this formula for *Field2*:

Technically, the IF formula function takes three *parameters*
(separated by commas in the formula above). The first parameter is a
condition, which must evaluate to either TRUE or FALSE, the second
parameter is the value to use if the condition is TRUE and the third
parameter is the value to use if the condition is FALSE. `Field1 < 10`

uses the
*logical operator* **<** (“less than”), so it
yields either TRUE or FALSE depending on the value of
*Field1*.

## Using multiple criteria

Now, what if you want to combine multiple criteria? Let’s say you
want to multiply *Field1* by 2 only if it is less than 10 but
is also greater than 2.

To combine multiple criteria, Excel users use the logical formula
functions AND, OR and NOT (also called *boolean* functions).
AND takes any number of parameters and returns TRUE if all parameters
are TRUE and FALSE otherwise. OR returns TRUE if one or several
parameters are TRUE and FALSE only if all parameters are FALSE. NOT
negates its one parameter, meaning that `NOT(TRUE)`

yields FALSE
and `NOT(FALSE)`

yields TRUE.
(There’s an additional logical formula function named XOR which you
can read more about in the reference sidebar.)

We can use the AND formula function to achieve the goal we set for
ourselves earlier, that is, changing the formula so that it only
multiplies *Field1* by 2 if it is less than 10 but also
greater than 2:

To multiply *Field1* by 2 only if its value is 3 or 5, use
this formula:

To multiply *Field1* by 2 only if its value is not 1, use this
formula:

(```
NOT(Field1 =
1)
```

is more naturally written as `Field1 <> 1`

or
```
Field1 !=
1
```

.)

## Using logical operators instead of logical functions

Calcapp also supports *logical operators* that are equivalent
to the logical functions AND, OR and NOT. An operator is the symbol
that typically appears between two numbers (known as
*operands*), such as the numeric operators **+**
(addition) and **/** (division). While those two
operators yield numbers, logical operators yield TRUE or FALSE. The
three operators that are equivalent to the AND, OR and NOT functions
are, in order, **&&**, **||** and
**!**. **&&** is used instead of AND,
and **||** is used instead of OR. Both are written
between their operands. **!** is used instead of NOT and
is written just before the operand.

This is what the formulas above look like when they are rewritten using operators:

We think that the logical operators read better than the logical functions, so we recommend using them. If you’re used to the traditional spreadsheet way of doing things, though, feel free to continue using the functions. (There’s no operator that is equivalent to the XOR formula function, because it is rarely used.)

## Complex rules

You can combine the logical operators or functions to create
arbitrarily complex rules. For instance, here’s what you would write
if you wanted to multiply *Field1* by 2 if *Field1* is
greater than 2 and is an even number, or if it is 5:

(```
MOD(Field1,
2)
```

divides *Field1* by 2 and returns the remainder. The
remainder is zero if the number is even and 1 if it is odd.)

Criteria can get arbitrarily complex. We created an app for a health
care company some time ago that needed complex validation logic to flag
potentially incorrect values. Here’s a complex formula we associated
with the
*Valid* property of the field *Sodium*:

```
(VolumeChoice = "Whole bag") &&
((((FirstOutputSupplement > 10) ||
(SecondOutputSupplement > 5) ||
((ThirdOutputSupplement > 10) && (BagVolume = 1000)) ||
(FourthOutputSupplement > 15)) &&
(((BagVolume = 775) && (Sodium * 4 + Phosphate * 2 > 32.0)) ||
((BagVolume = 1000) && (Sodium * 4 + Phosphate * 2 > 104.9)))) ||
(((BagVolume = 775) && (Sodium * 4 + Phosphate * 2 > 49.9)) ||
((BagVolume = 1000) && (Sodium * 4 + Phosphate * 2 > 104.9))))
```

This validation rule only applies if the user has selected “Whole
bag” from a drop-down
field. It references a large number of other fields to determine
what rules to use for the *Sodium* field. With a rule this
complex, it’s important that you use parentheses to clearly
communicate to Calcapp what your intentions are.

*Do you want to share a tip with other Calcapp users through this
blog? Let us know!*