=CALC

The =CALC() formula can be used to perform mathematical operations on any number or numeric field in your process.

Syntax

=CALC(MathematicalExpression)

Parameters

• MathematicalExpression

  • Any valid numeric expression built from numbers, numeric fields, and the operators listed below.

  • Use field tokens (e.g., [[Field: Field Number (Number)]]) to reference numeric fields.

Math Operators

Operator	What it does	Example	Result
+	Add	=CALC(2 + 3)	5
-	Subtract	=CALC(10 - 3)	7
*	Multiply	=CALC(7 * 3)	21
/	Divide	=CALC(10 / 4)	2.5
%	Remainder after dividing (mod)	=CALC(10 % 3)	1
^	Raise to a power	=CALC(2 ^ 3)	8
( )	Group, to control the order of operations	=CALC((2 + 3) * 4)	20

*, /, and % are worked out before + and -, and ^ before any of those. Parentheses override that order.

Remainders (mod) with %

The % operator gives you the remainder left over after dividing, which other tools call mod or modulo.

=CALC(10 % 3)

Returns

1

Three goes into ten three times, with 1 left over. A few things this is good for:

• Testing whether a number divides evenly. A remainder of 0 means it does. This checks whether an order is made up of full cases of 12:

  =IF(=CALC([[Field: Quantity (Number)]] % 12 = 0); Full cases; Partial case)

• Splitting a total into whole units plus a leftover. With 52 days, =CALC(52 % 7) returns 3, the days left over after 7 full weeks.

• Cycling through a repeating set. =CALC([[Field: Counter (Number)]] % 3) counts 0, 1, 2, 0, 1, 2 as the counter climbs, which is handy for rotating an assignment between three people.

Decimals are fine on either side, so =CALC(10.5 % 3) returns 1.5. With negative numbers the remainder takes the sign of the number on the left and ignores the sign on the right: =CALC(-10 % 3) returns -1, and =CALC(10 % -3) returns 1. A divisor of zero returns nothing, the same as dividing by zero.

Powers with ^

The ^ operator raises a number to a power.

=CALC(2 ^ 3)

Returns

8

Fractional powers work too, which is how you take a root: =CALC(9 ^ 0.5) returns 3. A negative power gives you the reciprocal, so =CALC(2 ^ -1) returns 0.5.

When you write a root as a fraction, put it in parentheses. =CALC(8 ^ (1/3)) returns 2, but =CALC(8 ^ 1/3) divides afterwards and returns 2.6666666666666666666666666667.

Two details worth knowing, both matching the way spreadsheets behave:

• A chain of powers is worked out from the right, so =CALC(2 ^ 3 ^ 2) is 2 to the power of 9, which is 512.

• ^ is applied before a minus sign in front of it, so =CALC(-2 ^ 2) returns -4. Write =CALC((-2) ^ 2) if you want 4.

Comparison and Logic Operators

=CALC can also compare two numbers. These return the word True or False, which =IF, =ISTRUE, and =ANYTRUE all accept.

Operator	What it does	Example	Result
=	Equal to	=CALC(3 = 3)	True
<>	Not equal to	=CALC(3 <> 3)	False
>	Greater than	=CALC(10 > 3)	True
<	Less than	=CALC(2 < 1)	False
>=	Greater than or equal to	=CALC(2 >= 2)	True
<=	Less than or equal to	=CALC(2 <= 1)	False
AND	True only when both sides are true	=CALC((1 > 0) AND (5 > 2))	True
OR	True when either side is true	=CALC((1 > 0) OR (1 > 2))	True
NOT	Reverses a comparison	=CALC(NOT (2 > 3))	True

Each side of AND, OR, and NOT has to be a comparison wrapped in parentheses. A bare number is not a condition, so =CALC(1 AND 0) returns nothing.

Note

For a plain comparison, the dedicated functions are easier to read: =GTNUM, =LTNUM, =GTENUM, =LTENUM, and =BNUM. Reach for the operators above when you want the comparison inline with arithmetic, as in the % 12 = 0 example.

How It Works

• Evaluates the provided mathematical expression and returns the resulting value.

• Powers run first, then multiplication, division, and remainders, then addition and subtraction, then comparisons, then AND, OR, and NOT. Parentheses override that order.

• The result can overwrite a field or be used in further calculations.

• When an expression cannot be worked out, =CALC returns nothing instead of an error message. A blank result is your signal that something in the expression is not valid.

Usage Notes

• Formula functions are case sensitive and must be entered in all caps.

• Ensure numeric fields are referenced correctly using field tokens.

• Parentheses can be used to control the order of operations.

• Commas and dollar signs are ignored, so a field holding $1,250.00 works without cleaning it up first.

• Dividing by zero returns nothing, so guard the divisor with =IF when it comes from a field someone fills in.

• Money arithmetic is exact. =CALC(0.1 + 0.2) returns 0.3, and trailing zeros are kept where they belong, so =CALC(1.10 * 3) returns 3.30.

• Division that does not come out evenly is carried to full precision, so =CALC(1 / 3) returns a long string of 3s. Wrap it in =ROUND or =FORMATNUM when the result is going to be read by a person.

• Comparisons cannot be chained. Write =CALC((1 < 2) AND (2 < 3)) rather than =CALC(1 < 2 < 3), which returns nothing.

Example

=CALC([[Field: Total (Number)]] / 2)

If the Total field contains 10, the formula returns 5.

Example

=CALC([[Field: Items (Number)]] % [[Field: Per Box (Number)]])

If Items contains 17 and Per Box contains 5, the formula returns 2, the items left over after filling 3 boxes.

---

Keywords

CALC, mathematical formula, numeric calculation, field calculation, process formulas, arithmetic operations, formula function, workflow calculations, mod, modulo, modulus, remainder, percent operator, divisible, even division, power, exponent, square, square root, operators, order of operations, comparison operators, AND OR NOT