Tip: Avoid gotchas when adding text boxes and buttons in the middle of a range

Ranges are an effective means of creating arrays containing many elements. However, it is easy to lose track of what elements they contain, which can have surprising results. This tip explores this topic in depth.

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(Field1:Field3)PRODUCT(Field1:Field3)

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:

PRODUCT({ Field1, Field2, Field3 })PRODUCT({ Field1; Field2; Field3 })

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:

PRODUCT({ Field1, Instructions, Field2, Field3 })PRODUCT({ Field1; Instructions; Field2; Field3 })

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({ Field1.Value, BLANK(), Field2.Value, Field3.Value })PRODUCT({ Field1,Value; BLANK(); Field2,Value; Field3,Value })

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:

PRODUCT(Field1:Field3 + 2)PRODUCT(Field1:Field3 + 2)

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:

PRODUCT({ Field1, Instructions, Field2, Field3 } + 2)PRODUCT({ Field1; Instructions; Field2; Field3 } + 2)

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:

PRODUCT({ Field1.Value + 2, BLANK() + 2, Field2.Value + 2, Field3.Value + 2 })PRODUCT({ Field1,Value + 2; BLANK() + 2; Field2,Value + 2; Field3,Value + 2 })

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:

FILTER(Field1:Field3, Field1:Field3 > 4)FILTER(Field1:Field3; Field1:Field3 > 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:

FILTER({ Field1, Field2, Field3 }, { Field1, Field2, Field3 } > 4)FILTER({ Field1; Field2; Field3 }; { Field1; Field2; Field3 } > 4)

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:

FILTER({ Field1, Instructions, Field2, Field3 }, { Field1, Instructions, Field2, Field3 } > 4)FILTER({ Field1; Instructions; Field2; Field3 }; { Field1; Instructions; Field2; Field3 } > 4)

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:

FILTER({ Field1, Instructions, Field2, Field3 }, { Field1.Value, BLANK(), Field2.Value, Field3.Value } > 4)FILTER({ Field1; Instructions; Field2; Field3 }; { Field1,Value; BLANK(); Field2,Value; Field3,Value } > 4)

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:

FILTER(Field1:Field3, Field1:Field3 > 4)FILTER(Field1:Field3; Field1:Field3 > 4)
FILTER(Field1:Field3, Element > 4)FILTER(Field1:Field3; Element > 4)

(The version using a formula fragment is a Calcapp extension. It has more in common with JavaScript’s filter() function than 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, on account of resembling traditional programming.

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

FILTER({ SwitchField1, NumberField1 }, Element.Visible)FILTER({ SwitchField1; NumberField1 }; Element,Visible)

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:

FILTER({ SwitchField1, NumberField1 }, Element.MinimumValue >= 0)FILTER({ SwitchField1; NumberField1 }; Element,MinimumValue >= 0)

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:

FILTER({ SwitchField1, NumberField1 }, { SwitchField1, NumberField1 }.MinimumValue >= 0)FILTER({ SwitchField1; NumberField1 }; { SwitchField1; NumberField1 },MinimumValue >= 0)

Here is the equivalent formula:

FILTER({ SwitchField1, NumberField1 }, { BLANK(), NumberField1.MinimumValue } >= 0)FILTER({ SwitchField1; NumberField1 }; { BLANK(); NumberField1,MinimumValue } >= 0)

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.

« Sample app: Convert spreadsheet tables to XLOOKUP formulas Tip: Debug arrays by inspecting their elements »