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	Y	The first value or condition that you want to evaluate for True or False
Input 2	N	The second value or condition that you want to evaluate for True or False
... Input N	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	Y	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	Y	Statement, value or logical operation evaluated for TRUE or FALSE
value_if_true	Y	Value returned if Condition is TRUE
value_if_false	N	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	Y	Statement, value or logical operation evaluated for TRUE or FALSE
value_if_true1	Y	Value returned if Condition is TRUE
Condition2	N	Optional statement, value or logical operation evaluated for TRUE or FALSE
value_if_true2	N	Optional value returned if Condition is TRUE
value_if_false	N	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	Y	The location of the data to be compared to input values
input1	Y	The input to be compared against the value in a specified field
value1	Y	The value returned when its corresponding input is equal to the value in a specified field
input2	N	A secondary input to be compared against the value in a specified field
value2	N	The value returned when its corresponding input is equal to the value in a specified field
default_value	N	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	Y	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	Y	The first value or condition that you want to evaluate for TRUE or FALSE
Input 2	N	The second value or condition that you want to evaluate for TRUE or FALSE
... Input N	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	Y	The first value or condition that you want to evaluate for TRUE or FALSE
Input 2	N	The second value or condition that you want to evaluate for TRUE or FALSE
... Input N	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	Y	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	Y	Statement or operation that is searched for the presence of Content value
Content	Y	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	Y	Statement, value or logical operation evaluated for ERROR
value_if_error	Y	Value returned if Condition has an ERROR
value_if_no_error	N	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	Y	Primary value for evaluation
Input2	Y	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	Y	Primary value for evaluation
Input2	Y	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	Y	Primary value for evaluation
Input2	Y	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	Y	Primary value for evaluation
Input2	Y	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")