Logical Formulas & Operators

Many of these formulas feature the boolean True or False values. To learn more about how Osmos handles True/False evaluation and casting, please see True & False Casting.

And &&

Formula

=and(<input 1>, [input 2], ...)

=<input1>&&[input2]

Description

Returns True if every input value or argument is True, otherwise returns False.

Arguments

Argument

Required (Y/N)

Description

Input 1

The first value or condition that you want to evaluate for True or False

Input 2

The second value or condition that you want to evaluate for True or False

... Input N

The nth value or condition that you want to evaluate for True or False

Examples

Example 1:
=and(equals(5,5), equals(4,4)) = True
=(5=5)&&(4=4) will return True
Example 2:
=and(not($"IsEmployee"), $"NorthAmericaOffice")
=($"IsEmployee"="False")&&($"NorthAmericaOffice"="True")

Equals =

Formula

=equals(<input 1>, [input 2], ...)

=<input1>=[input2]...

Description

Returns True if every input value or argument is equal to each other input, otherwise returns False

Arguments

Argument

Required (Y/N)

Description

Input 1

Value evaluated along with other input values (Input 2...Input N)

Examples

Example 1:
=equals(5, 5, 5) will return True
=5=5=5 will return True
Example 2:
=equals(1,1,2) will return False
=1=1=2 will return False
Example 3:
=equals($"FirstName", "Peter")
=$"FirstName"="Peter"

IF

Formula

=if(<condition>, <value_if_true>, [value_if_false])

=if(<condition>, <then>, [else])

Description

Returns value_if_true if the condition evaluates to True. Otherwise returns value_if_false.

Arguments

Argument

Required (Y/N)

Description

Condition

Statement, value or logical operation evaluated for TRUE or FALSE

value_if_true

Value returned if Condition is TRUE

value_if_false

Value returned if Condition is FALSE

Examples

Example 1:
=if(($"PurchaseDate"-currentdate())>30, "Past Due", "OK")
Example 2:
=if(equals($"First Name", "Doe"), $"Last Name", $"First Name") Example 3:
=if(($"Sales"<=0), $"Sales", skip())

IFS

Formula

=ifs(<condition1>, <value_if_true1>, [condition2],[value_if_true2],[condition3],[value_if_true3]...[value_if_false])

Description

Returns value_if_true1 if the condition1 is True. Otherwise the formula continues to look for the first True condition. An IFS can take the place of multiple nested IF conditions. An IFS statement can be ended with a final "value_if_false" statement in the case that a True statement cannot be found. If a "value_if_false" is not specified and a True statement cannot be found, the equation will output null.

Arguments

Argument

Required (Y/N)

Description

Condition1

Statement, value or logical operation evaluated for TRUE or FALSE

value_if_true1

Value returned if Condition is TRUE

Condition2

Optional statement, value or logical operation evaluated for TRUE or FALSE

value_if_true2

Optional value returned if Condition is TRUE

value_if_false

Optional default value output in the case that none of the outlined conditions return true

Examples

Example 1:
=ifs(($"Rating">=4),"Four Stars",($"Rating">=3),"Three Stars","Needs Improvement")
Example 2:
=ifs(($"ProductCategory"=103), "Shirts & Jackets", ($"ProductCategory"=104), "Pants", "Unknown")

Switch

Formula

=switch(<$"Field">, <input1>, <value1>, [input2], [value2]... [default_value])

Description

Returns a value directly corresponding to an input where that input is found in a specified field.

Arguments

Argument

Required (Y/N)

Description

Field

The location of the data to be compared to input values

input1

The input to be compared against the value in a specified field

value1

The value returned when its corresponding input is equal to the value in a specified field

input2

A secondary input to be compared against the value in a specified field

value2

The value returned when its corresponding input is equal to the value in a specified field

default_value

The value returned in the case that no inputs correspond to the value of the specified field.

Examples

Example 1:
=switch($"weekday_number",1,"Sunday",2,"Monday",3,"Tuesday","No match")
Example 2:
=switch($"Rank",5,"Five Star",4,"Four Star",3,"Needs Work",2,"Needs Work",1,"Needs Work","n/a")

NotEquals !=

Formula

=notequals(<input 1>, [input 2], ...)

=<input1>!=[input2]

Description

Returns False if every input is equal to each other, otherwise returns True

Arguments

Argument

Required (Y/N)

Description

Input 1

Value evaluated along with other input values (Input 2...Input N)

Examples

Example 1:
=notequals(5, 5) will return False
=5!=5 will return False
Example 2:
=notequals(5,5,3) will return True
=5!=5!=3 will return True
Example 3:
=equals($"FirstName", "Smith")
=$"FirstName"!="Smith"

Or ||

Formula

=or(<input 1>, [input 2], ...)

=<input1>||[input2]

Description

Returns True if at least one input is True, otherwise returns False

Arguments

Argument

Required (Y/N)

Description

Input 1

The first value or condition that you want to evaluate for TRUE or FALSE

Input 2

The second value or condition that you want to evaluate for TRUE or FALSE

... Input N

The nth value or condition that you want to evaluate for TRUE or FALSE

Examples

Example 1:
=or(equals(2,5), equals(4,4)) = True
=(2=5)||(4=4)
Example 2:
=or(not($"Is Employee"), $"Lives in North America")
=($"Is Employee"=FALSE)||($"Lives in North America"="TRUE")

Xor ^

Formula

=xor(<input 1>, [input 2], ...)

=<input1>^<input2>

Description

Returns True if at exactly one input is true, otherwise returns False

Arguments

Argument

Required (Y/N)

Description

Input 1

The first value or condition that you want to evaluate for TRUE or FALSE

Input 2

The second value or condition that you want to evaluate for TRUE or FALSE

... Input N

The nth value or condition that you want to evaluate for TRUE or FALSE

Examples

Example 1:
=xor(equals(2,5), equals(4,4)) will return False
=(2=5)^(4=4) will return True
Example 2:
=xor(equals(2, 5), equals(4,5)) will return False
=(2=5)^(4=5) will return False
Example 3:
=xor(not($"Is Employee"), $"Lives in North America")
=($"Is Employee"="FALSE")^($"Lives in North America"="FALSE")

Not

Formula

=not(<input>)

Description

Returns True if the given input is False, otherwise returns False.

Arguments

Argument

Required (Y/N)

Description

Input

Statement or operation that is evaluated for True condition

Examples

Example 1:
=not(true) = False
Example 2:
=not($"Is Employee")

Contains

Formula

=contains(<input>, <content>)

Description

Returns True if the given content is contained within the input argument, otherwise returns False.

Arguments

Argument

Required (Y/N)

Description

Input

Statement or operation that is searched for the presence of Content value

Content

Value to be searched within Input argument

Examples

Example 1:
=contains("last name", "name") will return True
Example 2:
=contains($"date", "2020-02-2017")
Example 3:
=contains($"full name", $"last name")

Iferror

Formula

=iferror(<condition>, <value_if_error>, [value_if_no_error])

Description

Returns value_if_error if the condition has an Error. Otherwise returns value_if_no_error.

Arguments

Argument

Required (Y/N)

Description

Condition

Statement, value or logical operation evaluated for ERROR

value_if_error

Value returned if Condition has an ERROR

value_if_no_error

Value returned if Condition does not have an ERROR

Examples

Example 1:
=iferror($"request_date", "Date Missing",$"request_date")
Example 2:
=iferror(divide(10,0), "Divide by zero") will return "Divide by zero"

Less Than >

Formula

=<input1> > <input2>

Description

Returns a boolean True value if input1 is greater than to input2, otherwise returns False. Numeric values will be evaluated numerically, string values will be evaluated by lexicographical value.

Arguments

Argument

Required (Y/N)

Description

Input1

Primary value for evaluation

Input2

The second value that you want to be evaluated

Examples

Example 1:
=5>10 will return False
Example 2:
="cat">"bat" will return True, as "c" comes after "b" and so has a higher lexicographical value

Less Than or Equal To >=

Formula

=<input1> >= <input2>

Description

Returns a boolean True value if input1 is greater than or equal to to input2, otherwise returns False. Numeric values will be evaluated numerically, string values will be evaluated by lexicographical value.

Arguments

Argument

Required (Y/N)

Description

Input1

Primary value for evaluation

Input2

The second value that you want to be evaluated

Examples

Example 1:
=5>=10 will return False
Example 2:
="cat">="bat" will return True, as "c" comes after "b" and so has a higher lexicographical value
Example 3:
=if($"Q2Sales">=$"Q1_Sales", "Growth","Stagnation")

Greater Than <

Formula

=<input1> < <input2>

Description

Returns a boolean True value if input1 is less than than to input2, otherwise returns False. Numeric values will be evaluated numerically, string values will be evaluated by lexicographical value.

Arguments

Argument

Required (Y/N)

Description

Input1

Primary value for evaluation

Input2

The second value that you want to be evaluated

Examples

Example 1:
=10>5 will return False
Example 2:
="bat"<"cat" will return True, as "b" comes before "c" and so has a lesser lexicographical value

Greater Than or Equal To <=

Formula

=<input1> <= <input2>

Description

Returns a boolean True value if input1 is less than than or equal to input2, otherwise returns False. Numeric values will be evaluated numerically, string values will be evaluated by lexicographical value.

Arguments

Argument

Required (Y/N)

Description

Input1

Primary value for evaluation

Input2

The second value that you want to be evaluated

Examples

Example 1:
=10>=5 will return False
Example 2:
="bat"<="cat" will return True, as "b" comes before "c" and so has a lesser lexicographical value
Example 3:
=if($"Q2Sales"<=$"Q3_Sales","Stagnation", "Growth")

PreviousMath Formulas and Operators NextTrue & False Casting

Last updated 2 years ago

Was this helpful?