Logical or operator (||)

Value1 || Value2

Value1 — Logical or { Logical }

The first value to compare.

Value2 — Logical or { Logical }

The second value to compare.

Returns — Logical or { Logical }

TRUE if one or both values are TRUE and FALSE otherwise.

Returns TRUE if one or both values are TRUE and FALSE otherwise. (Field1 = 3) || (Field2 < 10)(Field1 = 3) || (Field2 < 10) returns TRUE if Field1.ValueField1,Value is equal to 3 or Field2.ValueField2,Value is less than 10, otherwise FALSE is returned.

Use ||, && and ! in conjunction with IF to express logical conditions.

This formula returns 2 only if Field1.ValueField1,Value is 3 or Field2.ValueField2,Value is less than 10, and 4 otherwise:

IF((Field1 = 3) || (Field2 < 10), 2, 4)IF((Field1 = 3) || (Field2 < 10); 2; 4)

The formula can also be written using the OR function:

IF(OR(Field1 = 3, Field2 < 10), 2, 4)IF(OR(Field1 = 3; Field2 < 10); 2; 4)

|| is written between the values to test, instead of before them, which can make formulas easier to read.

|| and arrays

|| behaves differently from OR when used with arrays. OR always returns a single TRUE or FALSE value when applied to arrays, but || instead returns an array.

This formula returns TRUE, because at least one array element is TRUE:

OR({ FALSE, FALSE, TRUE }, { FALSE, TRUE, FALSE })OR({ FALSE; FALSE; TRUE }; { FALSE; TRUE; FALSE })

This formula, on the other hand, returns the array { FALSE, TRUE, TRUE }{ FALSE; TRUE; TRUE }:

{ FALSE, FALSE, TRUE } || { FALSE, TRUE, FALSE }{ FALSE; FALSE; TRUE } || { FALSE; TRUE; FALSE }

The formula above is equivalent to this formula:

{ FALSE || FALSE, FALSE || TRUE, TRUE || FALSE }{ FALSE || FALSE; FALSE || TRUE; TRUE || FALSE }

In other words, || returns an array with the same size as the two arrays given to it. || then compares the values of the two arrays that are at the same positions and puts TRUE at that position in the result array if one or both values are TRUE.

|| is specific to Calcapp. Spreadsheet users typically use the + operator instead to get the array behavior offered by ||. Calcapp also supports the + operator for use with arrays.

|| and FILTER

The array behavior of || is useful when used with functions like FILTER. One version of FILTER takes an array to be filtered as its first parameter and a logical array as its second parameter which determines what elements of the first array are included in the result array.

Consider this formula:

FILTER({ 1, 2, 3, 4 }, { TRUE, FALSE, TRUE, FALSE })FILTER({ 1; 2; 3; 4 }; { TRUE; FALSE; TRUE; FALSE })

The formula above returns { 1, 3 }{ 1; 3 }, because 1 and 3 of the first array correspond to TRUE in the second array, unlike 2 and 4, which correspond to FALSE in the second array.

|| is perfect for creating a logical array to be used as the second parameter to FILTER. This formula returns an average of the test scores obtained by students who are either female or reside in Arkansas (abbreviated "AK"):

AVERAGE(FILTER({ 90, 62, 91, 82 }, ({ "F", "M", "M", "F" } = "F") || ({ "CA", "CA", "AZ", "AK" } = "AK")))AVERAGE(FILTER({ 90; 62; 91; 82 }; ({ "F"; "M"; "M"; "F" } = "F") || ({ "CA"; "CA"; "AZ"; "AK" } = "AK")))

The second parameter, ({ "F", "M", "M", "F" } = "F") || ({ "CA", "CA", "AZ", "AK" } = "AK")({ "F"; "M"; "M"; "F" } = "F") || ({ "CA"; "CA"; "AZ"; "AK" } = "AK") applies || to the two arrays. This is the first parameter and the array it is equivalent to:

({ "F", "M", "M", "F" } = "F")({ "F"; "M"; "M"; "F" } = "F")
{ TRUE, FALSE, FALSE, TRUE }{ TRUE; FALSE; FALSE; TRUE }

They are equivalent because only the first and last text strings are equal to "F".

This is the second parameter and the array it is equivalent to:

{ "CA", "CA", "AZ", "AK" } = "AK"{ "CA"; "CA"; "AZ"; "AK" } = "AK"
{ FALSE, FALSE, FALSE, TRUE }{ FALSE; FALSE; FALSE; TRUE }

They are equivalent because only the last text string is equal to "AK".

In effect, || is applied to these two arrays:

{ TRUE, FALSE, FALSE, TRUE }{ TRUE; FALSE; FALSE; TRUE }
{ FALSE, FALSE, FALSE, TRUE }{ FALSE; FALSE; FALSE; TRUE }

The result is the array { TRUE, FALSE, FALSE, TRUE }{ TRUE; FALSE; FALSE; TRUE }, because only the elements that appear first and last in the two arrays are such that at least a single one is TRUE.

In effect, the || and = operators work in tandem to return a logical array which is TRUE in the positions where either the corresponding value in the first array is "F" or the corresponding value in the second array is "AK". That, in turn, causes FILTER to only return test scores for students that are either female or reside in Arkansas. Here, two test scores match and are returned ({ 90, 82 }{ 90; 82 }. AVERAGE then returns the average of those two data points, 86.

Examples

TRUE || FALSETRUE || FALSE

Returns TRUE.

FALSE || TRUEFALSE || TRUE

Returns TRUE.

FALSE || FALSEFALSE || FALSE

Returns FALSE.

TRUE || TRUETRUE || TRUE

Returns TRUE.

(Field1 = 3) || (Field2 < 10)(Field1 = 3) || (Field2 < 10)

Returns TRUE if Field1.ValueField1,Value is equal to 3 or Field2.ValueField2,Value is less than 10, otherwise FALSE is returned.

OR(Field1 = 3, Field2 < 10)OR(Field1 = 3; Field2 < 10)

Returns TRUE if Field1.ValueField1,Value is equal to 3 or Field2.ValueField2,Value is less than 10, otherwise FALSE is returned. OR is equivalent to || when arrays are not used.

OR({ FALSE, FALSE, TRUE }, { FALSE, TRUE, FALSE })OR({ FALSE; FALSE; TRUE }; { FALSE; TRUE; FALSE })

Returns TRUE, because at least one array element is TRUE.

{ FALSE, FALSE, TRUE } || { FALSE, TRUE, FALSE }{ FALSE; FALSE; TRUE } || { FALSE; TRUE; FALSE }

Returns the array { FALSE, TRUE, TRUE }{ FALSE; TRUE; TRUE }, which is equivalent to { FALSE || FALSE, FALSE || TRUE, TRUE || FALSE }{ FALSE || FALSE; FALSE || TRUE; TRUE || FALSE }.

{ FALSE, FALSE, TRUE } + { FALSE, TRUE, FALSE }{ FALSE; FALSE; TRUE } + { FALSE; TRUE; FALSE }

Returns the array { FALSE, TRUE, TRUE }{ FALSE; TRUE; TRUE }, which is equivalent to { FALSE || FALSE, FALSE || TRUE, TRUE || FALSE }{ FALSE || FALSE; FALSE || TRUE; TRUE || FALSE }. +, when used with arrays, is equivalent to ||, and is what spreadsheet users are used to.

FILTER({ 1, 2, 3, 4 }, ({ 1, 2, 3, 4 } > 3) || ISODD({ 1, 2, 3, 4 }))FILTER({ 1; 2; 3; 4 }; ({ 1; 2; 3; 4 } > 3) || ISODD({ 1; 2; 3; 4 }))

Returns { 1, 3, 4 }{ 1; 3; 4 }, which is a filtered version of the { 1, 2, 3, 4 }{ 1; 2; 3; 4 } array, where only elements which are greater than 3 or are odd are kept. Only 1, 3 and 4 qualify, and as a result are the only elements that are part of the filtered array.

FILTER({ 1, 2, 3, 4 }, (Element > 3) || ISODD(Element))FILTER({ 1; 2; 3; 4 }; (Element > 3) || ISODD(Element))

Returns { 1, 3, 4 }{ 1; 3; 4 }, which is a filtered version of the { 1, 2, 3, 4 }{ 1; 2; 3; 4 } array, where only elements which are greater than 3 or are odd are kept. Only 1, 3 and 4 qualify, and as a result are the only elements that are part of the filtered array. This example uses a different version of FILTER, one that uses a formula fragment to determine which items are kept. This FILTER version is a Calcapp extension.