Expression: Logical Functions

If

If (A,B,C) - if condition A then use value B else use value C.

If (A,B) - if condition A then use value B else use missing value.

If( [Cost] < 1000 , 1, 0)

If( AgeWeeks([Booking Date]) <= 52, 0, 69)

Case

Case(A,a,B,b,C,c ...) – if condition A then use value a, condition B then use value b, etc..

Case( [Cost] = 100, 1,

      [Cost] = 200, 2,

      [Cost] = 300, 3)

Case( [Cost] < 500, 1,

Or

Or(A,B,C...)  - evaluates to  true if condition A is true OR condition B is true etc., otherwise evaluates to false.

If( Or ([Cost]<1000,[Cost]=1000),1,0)

And

And(A,B,C...) - evaluates to  true if condition A is true AND condition B is true etc., otherwise evaluates to false.

If( And([Cost]>500,[Cost]<1000),1,0)

InList

InList(X,A,B,C ...) - greater than 1 if expression X takes value of expression A or B or C ...

If( InList(790.00, [Cost],[Cost]+10,[Cost]-10),1,0)

This will return 1 if [Cost] is 790, 2 if ([Cost]+10) is 790, etc.

InListAllMatches(Expression, Value 1, Value 2, …)

Calculates the value of the “Expression” defined in the first parameter. The result of this expression can be a numeric, string or date type. The “Values” are then checked against the result.

Returns a numeric list containing the index of all the value parameters which match the expression result. If there is no match then empty list is returned. You can also set the match parameters from a list.

e.g. InListAllMatches(Product Code, "ABC", "DEF", "GHI") - returns a numeric list with 1 in it if the Product Code is ABC, 2 in it if it is DEF, 3 in it if it is GHI, and an empty list if none of them match.

Index

Index(X,A,B,C ...) selects Xth item (1-based) from the list (A,B,C ...).

Index(2,1,2,3) returns 2

The match parameters can also be set from a list.

IndexFromList(NumericList Expression, Value 1, Value 2, …)

Calculates the values of the “NumericList Expression” defined in the first parameter.

Returns a list of the values represented by the corresponding positions in the list of "Values". You can also set the match parameters from a list.

e.g. IndexFromList(CreateList(2, 3), "a", "b", "c") - returns a list with "b" and "c" in it.

Returns the index of the maximum value in a list of values

MaxIndex(4,9,2,4,5) returns 2

The second number is the highest in this list

Returns the index of the minimum value in a list of values

MinIndex(4,9,2,4,5) returns 3

The third number is the lowest in this list

Returns the logical opposite of the input logical expression

10 > 5 returns 1 (true)

Not(10 > 5) returns 0 (false)

Determines whether an email address is in a valid format

ValidEmail([Email Address])

Returns 1 if true and 0 if false