Logical and operator (&&)

Returns TRUE if both values are TRUE and FALSE otherwise. (Field1 = 3) && (Field2 < 10)(Field1 = 3) && (Field2 < 10) returns TRUE only if Field1.ValueField1,Value is equal to 3 and 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 20 and Field2.ValueField2,Value is not 40, and 4 otherwise:

The formula can also be written using the AND and NOT functions:

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

&& and arrays

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

This formula returns FALSE, because not all array elements are TRUE:

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

The formula above is equivalent to this formula:

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 only puts TRUE at that position in the result array if both those 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:

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 female students who reside in Arkansas (abbreviated "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:

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:

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

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

The result is the array { FALSE, FALSE, FALSE, TRUE }{ FALSE; FALSE; FALSE; TRUE }, because only the two elements that appear last in the two arrays are both TRUE.

In effect, the && and = operators work in tandem to return a logical array which is only TRUE in the positions where the corresponding value in the first array is "F" and the corresponding value in the second array is "AK". That, in turn, causes FILTER to only return test scores for students that are female and reside in Arkansas. Here, only one test score matches and is returned ({ 82 }{ 82 }), because while two female students appear in the data, only one of the students resides in Arkansas. AVERAGE then averages that single data point, 81, and returns it unchanged.

Examples