Arrays are arguably the biggest addition to our latest Calcapp release. They form a core part of the many new functions and properties, and make Calcapp much more powerful.

However, when used with ranges, it’s easy to lose track of what elements are actually included in an array. That can have surprising results, which the topic for this post.

## Blank values and ranges

Consider the following formula:

PRODUCT multiplies its parameters together and returns the result. SUM is similar, but adds numbers together instead.

Remember that the range Field1:Field3Field1:Field3 is equivalent to
the array { Field1,
Field2, Field3 }{ Field1; Field2;
Field3 }, but only if *Field2* is the only element
that appears between *Field1* and *Field3*. If that is
the case, this formula is equivalent to the one above:

What happens if you add a text box, named *Instructions*,
right underneath *Field1*?

That change affects the Field1:Field3Field1:Field3 range. Instead of expanding to the array { Field1, Field2, Field3 }{ Field1; Field2; Field3 }, it expands to the array { Field1, Instructions, Field2, Field3 }{ Field1; Instructions; Field2; Field3 }.

In other words, what Calcapp sees is this formula:

Calcapp could show an error here, complaining that while
*Field1*, *Field2* and *Field3* can be treated
as numbers (through their Value properties),
*Instructions* cannot, and therefore must not be used with
PRODUCT.

Showing an error wouldn’t be helpful, though, because it would mean that formulas like the one above would break the moment you add a text box or button in the middle of a range. Thankfully, Calcapp is forgiving when handling arrays, and no error is shown.

Instead, PRODUCT is given an array, consisting of the values of the fields and a blank value for the text box, as illustrated by this formula:

PRODUCT ignores the blank value, and returns the product of the field values, as expected. Had it not ignored the blank value, and instead treated it as zero, the returned value would have been zero.

It would arguably be surprising behavior if adding a text box in the middle of the Field1:Field3Field1:Field3 range caused formulas to seemingly malfunction, so it’s a good thing that PRODUCT ignores blank values.

## Blank values are not always ignored

So far, everything works the way we expect it to. However, what happens if we try to add 2 to the elements of an array?

Consider this formula:

The formula above applies the PRODUCT function to the array that results from adding 2 to the Field1:Field3Field1:Field3 range.

When the range is expanded, the following equivalent formula is the result:

When the value to the left of the + operator is an array and the value to its right is a number — which is the case above — the number is added to each element of the array.

*Field1*, *Field2* and *Field3* are number
fields. 2 cannot be added directly to them, but can be added to their
Value properties. That
causes Field1 +
2Field1 + 2
to be expanded to Field1.Value +
2Field1,Value
+
2.

*Instructions* is a text box, though, and it does not have a
number property. That causes Calcapp to replace it with a blank value
before adding 2 to it: BLANK() + 2BLANK() + 2.

Here’s the fully-expanded formula:

In BLANK() + 2BLANK() + 2, the + operator treats a blank value as zero, and therefore returns 2. That means that PRODUCT longer ignores the text box that appears in the array, as the second value passed to PRODUCT is 2 and not a blank value, causing the result to be twice the number you might expect.

In other words, if you use a formula like PRODUCT(Field1:Field3 + 2)PRODUCT(Field1:Field3 + 2), you’ll get different results depending on if you add a text box or button in the middle of the range. If your app returns puzzling results and you use ranges, this is important to be aware of.

If you are unsure what array elements are included in a range, create a temporary field whose only purpose is printing the elements of the array. That is the topic for this tip.

## Blank array elements and FILTER

Blank values in arrays and ranges can also cause surprising results when used with the FILTER function.

To recap, FILTER accepts an array as its first parameter and returns a version of this array which only contains elements that have passed a test you have supplied.

The most commonly used variant of FILTER expresses this test as a logical array given as the second parameter, which should contain the same number of elements as the array to filter. If an element of the second array is TRUE, the corresponding element of the array to filter is part of the returned array. If it is FALSE, the element is dropped.

This formula returns an array of fields whose values are greater than 4:

(The Field1:Field3 > 4Field1:Field3 > 4 formula fragment returns the logical array expected by FILTER. Consult our documentation for the > operator to learn more.)

The formula above expands to this formula, if *Field3*
directly follows *Field2*, which directly follows
*Field1*:

Now, let’s once more consider what happens when the text box
*Instructions* appears just below *Field1*. If that is
the case, the first formula of this section — using the range
Field1:Field3Field1:Field3 — is equivalent to
this formula:

The > operator can only work
with numbers, logical values, text strings and colors. The number
fields can return their values to satisfy
these requirements, but *Instructions* is a text box and
cannot do the same. As a result, it is replaced by a blank value.

The formula above expands to this formula:

In other words, the first element of the array to filter —
*Field1* — is only kept if Field1.ValueField1,Value is
greater than 4, and likewise for the other fields. The second element
of the array to filter — *Instructions* — is only kept if
BLANK()BLANK() is greater than 4
(which it is not, as the > operator treats a blank
value as zero).

Therefore, somewhat counter-intuitively, *Instructions* is not
part of the returned array because it is not considered to be greater
than 4. Had the condition been < 4< 4 instead, it would
have been included, as the < operator would have treated
it as the value zero.

## FILTER used with formula fragments works differently

There is a variant of FILTER that doesn’t take a logical
array as its second parameter, but a formula fragment. This fragment
is invoked once per array element, with the element under
consideration available as `Element`

, and is
expected to return TRUE if the element should be included and FALSE
otherwise.

These two formulas are equivalent if the Field1:Field3Field1:Field3 range only contains number fields:

(The version using a formula fragment is a Calcapp extension. It has more in common with JavaScript’s filter() function than with Excel’s FILTER function.)

However, if the Field1:Field3Field1:Field3 range also includes a text box, the first formula above will work, but you will get an error message from the second version in Calcapp Creator, preventing you from sharing your app. The reason is that formula fragments are more strict than arrays.

To get a better feeling for how this works, consider this formula:

This formula returns an array consisting of the fields of the first array which are visible. It works, because both number fields and switch fields support the Visible property.

Now consider this formula:

The MinimumValue is a property of number fields. As this property is not supported by switch fields, Calcapp Creator will show an error message.

However, if we use a logical array instead, the formula will work with no errors:

Here is the equivalent formula:

The formula above returns an array containing both
*SwitchField1* and *NumberField1*, because the blank
value is treated as zero by the >= operator,
which passes the >=
0>= 0
test.