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!*