Search

Operators and Constants

Operators

(Note the single quotes round the item name if it contains a number or operator such as a hyphen or the words IF AND OR etc. This applies to all formulas.)

+ Add Cabbages+Carrots+'Mange-Tout'+'Oranges and Lemons'
- Minus Sales-Cost of Goods
/ Divide: Gives the result of zero rather than an error if the divisor is zero. Profit/Sales
* Multiply Price * Units
> Greater than: If x is greater than 1000 then 1 else 2. Can be used to compare numbers, dates, or text.
If you want to compare number values which are formatted as text, we strongly recommend that you use VALUE to convert to numeric.
IF x>1000 THEN 1 ELSE 2.
IF VALUE(x) > VALUE(y) THEN 1 ELSE 0, where x and y are text formatted with number values entered.
< Less than: If x is less than 1000 then 1 else 2. Can be used to compare numbers, dates, or text.
If you want to compare number values which are formatted as text, we strongly recommend that you use VALUE to convert to numeric.
IF x<1000 THEN 1 ELSE 2
IF VALUE(x) < VALUE(y) THEN 1 ELSE 0, where x and y are text formatted with number values entered.
>= Greater than or equal to: E.g. If x is greater than or equal to1000 then 1 else 2. Can be used to compare numbers, dates, or text.
If you want to compare number values which are formatted as text, we strongly recommend that you use VALUE to convert to numeric.
IF x>=1000 THEN 1 ELSE 2
IF VALUE(x) >= VALUE(y) THEN 1 ELSE 0, where x and y are text formatted with number values entered.
<= Less than or equal to: E.g. If x is less than or equal to1000 then 1 else 2. Can be used to compare numbers, dates, or text.
If you want to compare number values which are formatted as text, we strongly recommend that you use VALUE to convert to numeric.
IF x<=1000 THEN 1 ELSE 2
IF VALUE(x) <= VALUE(y) THEN 1 ELSE 0, where x and y are text formatted with number values entered.
= Equals: E.g. If x is equal to 1000 then 1 else 2. Can be used to compare numbers, dates, lists, text or boolean provided they are of the same data type. IF x=1000 THEN 1 ELSE 2
<> Not Equal: E.g. If x is not equal to 1000 then 1 else 2. Can be used to compare numbers, dates, lists, text or boolean provided they are of the same data type. IF x<>1000 THEN 1 ELSE 2
& Concatenates two or more text strings. E.g. If x is a text formatted cell containing "blue", Result="light" & x gives result of "light blue" "text_string" & x

Constants - TRUE, FALSE and BLANK

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

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