Handling nested array with CSE

Question

Hi all,

Today we have already had the dynamic array. But in some interesting cases I just found out, the dynamic array is not the solution. Let's begin with this function:

=INDEX({1;2;3;4;5;6},{1;2;3;4})

Quite easy to understand, right? It returned an array with 4 elements: 1, 2, 3 and 4.

Well, let's find out by wrapping in the SUM function, very basic.

So in dynamic array it will calculate the total of 1, 2, 3, 4 and it is equal to 10.

But in "legacy" array formula (or CSE in short), it returned differently:

Why 1, but not 10? - I also asked myself that.

And if I choose 4 cells and enter the CSE, it returned:

Then a concept of nested array appeared in my mind. And I write another function to check it:

=INDEX({1,2,3;4,5,6},{1;2},0)  

Theoretically, it gets all the number of row 1 (1,2,3) and row 2 (4,5,6) as the result. But Excel right now cannot display that. So, in dynamic array, it showed only {1;4}, which is the first item of each child-array (I forgot the term to mention array inside array).

If I wrap it in SUM function and hit Enter, it will return 5.

But if I use CSE, it will return 6. A bit weird, right?

And continue, if I use CSE in 2 cells, it will return 6 and 15

What is 6, and what is 15?

They are the total of each child-array {{1,2,3};{4,5,6}}.

Now back to first example, I really assume the =INDEX({1;2;3;4;5;6},{1;2;3;4}) return something like {{1};{2};{3};{4}} instead of {1;2;3;4}

So, in quick conclusion, I would say that there are several ways we can deal with the nested array in Excel using CSE.

Before finishing this writing, how about giving some examples?

Example 1: We can use the MAX function with CSE to compare one value to a set of values. If SUM performed a total for each child-array, maybe the MAX can also do that?

Yes. It could.

Example 2: We can calculate the running total by each row of a 2-D range with just one formula using CSE.

Go through the array with INDEX, then wrap it in CSE.

I also have some notes myself:

-The LOOKUP function does not return nested array. For example =LOOKUP({1;2},{1;2;3;4},{5;6;7;8}) returns {5;6} instead of {{5};{6}}.

-The VLOOKUP function has some other constrain related to number of cell in CSE that I have not found out yet. When I try to CSE with more than 1 cell, it has the same result with dynamic array. In other case, it behaves like CSE.

-The ROW function with range in Office 2013 returns nested array, but in Office 365 it does not. For example: ROW(1:3) in Office 2013 behaves differently than that in Office 365.

...

Answer 1

I agree that it is sometimes very difficult to predict how a given formula will behave. The closest one gets to dynamic arrays in traditional Excel is when range formulas are calculated within Name Manager or, as you have done the ranges are converted into explicit array constants. Even then, one difference is that CSE arrays support array breakup allowing

= range + offset

where offset overlaps the result range.

Without CSE that would be a circular reference. Helper functions such as SCAN go a long way towards sorting the problems but there are still one or two issues outstanding.