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

    You should 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-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-formatted value to a date-formatted one, 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-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-formatted value from a date-formatted one, 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-formatted items.

    Price * Units
    /

    Divides items.

    Compatible with number-formatted items.

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

    AND

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

    Compatible with Boolean-formatted items.

    x > 1000 AND y >1000
    OR

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

    Compatible with Boolean-formatted items.

    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 format.

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

    Learn more about: