Excel Range vs Array

As I worked more with LAMBDA, I discovered that Excel functions that take range parameters do not work with arrays.

If a function's parameter mentions "range", you can be sure this will not work with arrays. Some examples of these functions are COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, SUMIF, and SUMIFS. The other that I am aware of is RANK.AVG.

What is an Array?

A Range represents a cell, a row, a column, a selection of selections, or blocks of cells. Range will always have an address, for example B2:B5 is a range of values stored in a column.

An Array is a set of values constructed using a formula. For example ={1;2;3;4} is column of values. =VSTACK(1,2,3,4) is also a column of values.

The FORMULATEXT function would return #N/A in a range cell that contains only values. On the other hand it returns the formula used to construct the array values.

An array can be displayed on a spreadsheet. When displayed on the spreadsheet, the values can be referred by the location in the cell it occupies.

In the above screenshot, you can see SUMIF works on the range as well as the displayed array. This is because the displayed array has an address.

However passing an array directly into the function would fail. In the example below the SUMIF fails for an array.

Currently, we can say Excel functions that take range parameters will not work with arrays.

Then why use Arrays?

While previous examples in this blog used intermediate values to improve readability, you could technically construct those same function all within a single line of instruction.

This will not always be the case. At times intermediate values are necessary, and at times, intermediate arrays are necessary. And you will encounter road blocks if SUMIF, SUMIFS, COUNTIF, or COUNTIFS is used with arrays.

However, when you run into these situations, do not despair. Relook into the approach used. I found matrix calculation and SUMPRODUCT approaches works, albeit less intuitive.

NOTE: When constructing arrays, use VSTACK and HSTACK instead of curly bracket arrays {}. Arrays defined using curly brackets {} may not work in some Excel functions. Rather than spending time figuring which conditions they work, just use the VSTACK and HSTACK.

For now, just remember, there's always a solution for you in DC-DEN!