1. Calculation functions
  2. Operators and constants

Use operators and constants with your data to create calculations and find the data you need.

Operators

Use operators in your formulas to combine, compare, and perform mathematical or logical operations with your data.

Each type of operator can only be used with certain types, or combinations, of data. However, you can use multiple operators and their compatible data types in a single formula.

Enclose list or line item names in single quotes if they contain:

  • a number
  • an operator such as a hyphen or asterisk
  • the words IF, AND, or OR

Use quotes to ensures that characters in a list or line item name do not affect your formulas and operators.

OperatorDescriptionExample
+

Adds items.

Use with number, date, and time period values.

You can add a number to a number, or a number to a date, but not a date to a date.

If you add a number type value to a date type value, the date moves forward in time by that number of days.

Cabbages + Carrots + 'Mange-Tout' + 'Oranges and Lemons'
-

Subtracts items.

Compatible with number and date values.

You can subtract a number from a number, a number from a date, or a date from a date.

If you subtract a number type value from a date type value, the date moves back in time by that number of days.

If you subtract a date from a date, it returns a number, showing the number of days between each date.

Sales - Cost of Goods
*

Multiplies items.

Compatible with number values.

Price * Units
/

Divides items.

Compatible with number values.

If the divisor is zero, the operator returns zero as the result (the DIVIDE function returns Infinity).

Profit / Sales
>

Determines if the left item has a greater value than the right item. Used in formulas that contain IF.

Compatible with number, date, time period, or text values. Both items used with this operator must be of the same data type.

IF x > 1000 THEN a ELSE b

IF “Abc” > “abc” THEN a ELSE b

<

Determines if the left item has a smaller value than the right item. Used in formulas that contain IF.

Compatible with number, date, time period, or text values. Both items used with this operator must be of the same data type.

IF x < 1000 THEN a ELSE b

IF “Abc” < “abc” THEN a ELSE b

>=

Determines if the left item has a greater or equal value to the right item. Used in formulas that contain IF.

Compatible with number, date, time period, or text values. Both items used with this operator must be of the same data type.

IF x >= 1000 THEN a ELSE b

IF “Abc” >= “abc” THEN a ELSE b

<=

Determines if the left item has a smaller or equal value to the right item. Used in formulas that contain IF.

Compatible with number, date, time period, or text values. Both values used with this operator must be of the same data type.

IF x <= 1000 THEN a ELSE b

IF “Abc” =< “abc” THEN a ELSE b

=

Determines if two items are equal. Used in formulas that contain IF.

Can be used to compare number, Boolean, date, time period, or text values.

IF x = 1000 THEN a ELSE b
<>

Determines if two items are not equal. Used in formulas that contain IF.

Can be used to compare number, Boolean, date, time period, or text values.

IF x <> 1000 THEN a ELSE b
&

Concatenates two or more text strings.

To ensure characters in the concatenated strings do not affect your formulas, enclose them in quotation marks:

  • Enclose references to text-formatted items in single quotation marks
  • Enclose text entered directly into a formula in double quotation marks.

When you concatenate large numbers of, or long, text strings, it can have a negative impact on model performance. Consider the use of alternative data formats to improve performance.

'Text-formatted line item' & x

"text_string" & x

AND

Tests whether the conditions before and after the AND operator are both met.

Compatible with Boolean values.

x > 1000 AND y >1000
NOT

Tests whether the condition before but not after the NOT operator is met.

Compatible with Boolean values.

x > 1000 NOT y > 1000
OR

Tests whether only one of the conditions before and after the OR operator is met.

Compatible with Boolean values.

x > 1000 OR y > 1000

Constants (TRUE, FALSE, and BLANK)

The constants TRUE and FALSE are provided for use as the result of conditional calculations. TRUE and FALSE resolve to Boolean.

The constant BLANK is the default null value for the list, text, time period, or date data types.

Learn more about: