1. Calculation functions
  2. Operators and Constants

Operators

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

Each type of operator can only be used with certain types, or combinations, of data. However, in a formula that contains operators, you can use items with multiple data types.

Use single quotes before and after item names if they contain:

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

This ensures that characters in an item name do not affect your formulas and operators.

Operator Description Examples
+

Adds items.

Compatible with number-, date-, time period-formatted items.

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 to a date, the date changes to be that number of days later.

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

Subtracts items.

Compatible with number- and date-formatted items.

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

If you subtract a number from a date, the date changes to be that number of days earlier.

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-formatted items.

Price * Units
/

Divides items.

Compatible with number-formatted items.

If the divisor is zero, 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-formatted items. Both items used with this operator must be of the same data type.

IF x > 1000 THEN 1 ELSE 2

IF “Abc” > “abc” THEN 1 ELSE 0

<

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-formatted items. Both items used with this operator must be of the same data type.

IF x < 1000 THEN 1 ELSE 2

IF “Abc” < “abc” THEN 1 ELSE 0

>=

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-formatted items. Both items used with this operator must be of the same data type.

IF x >= 1000 THEN 1 ELSE 2

IF “Abc” >= “abc” THEN 1 ELSE 0

<=

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-formatted items. Both items used with this operator must be of the same data type.

IF x <= 1000 THEN 1 ELSE 2

IF “Abc” =< “abc” THEN 1 ELSE 0

=

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

Can be used to compare number-, Boolean-, date-, time period-, or text-formatted items.

IF x = 1000 THEN 1 ELSE 2
<>

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-formatted items.

IF x <> 1000 THEN 1 ELSE 2
&

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

Constants (TRUE, FALSE, and BLANK)

These constants are primarily provided for use as the result of conditional calculations.

TRUE and FALSE resolve to Boolean format.

BLANK is the default null value for LIST, TEXT, TIME PERIOD, or DATE data types.

Comparison operator behavior

The <, >, <=, >=, =, and <> operators can compare number-, date-, time period-, or text-formatted items (The = and <> can also compare Boolean-formatted items). The comparison method varies between these data types.

Number comparison

When you use a comparison operator to compare numeric items, it considers the number that is closer to positive infinity as greater. For example, this means that 1 is greater than -1,000,000,000.

Date and time period comparison

When you use a comparison operator to compare date- or time period-formatted values, it considers the later date as greater.

You cannot compare a date- and time period-formatted value as they are not the same data type. If you need to, convert between data types.

Text comparison

The comparison of text-formatted items follows lexicographical order. As such, when a comparison operator compares text-formatted values, it considers strings with fewer characters as greater than strings with more.

For two strings of equal length, the operator compares the first character that differs from left to right to determine which string has a greater value. The types of character and criteria below, in order, give a text string a greater value if they're the first character that differs:

  1. Special characters used in non-English languages
  2. Capitalization (capitals > lowercase)
  3. Alphabetical order (a > z)
  4. Special characters such as punctuation
  5. Numeric order (0 > 9)