Excel Nested Arrays: Find the Nesting Depth of Uniform and Ragged Arrays ARRDEPTH() & ISRAGGED()

Thank you for the comment. The ambiguity has been partly fixed. I like that ARRDEPTH returns an error when branches are not equal. This is also the industry standard in NumPy.

However, it has a limitations.

As I mentioned above in my reply to someone else, it is too rigid for Excel. That is why I split it into two functions: UNIDEPTH and RAGDEPTH. This gives users complete freedom to choose which one to use, without forcing redundant calculations on uniform arrays. To my surprise when I tested it, the performance effect was minimal, so this may not be major limitation after all.

So, what stops us from always using one form?

RAGDEPTH errors on arrays more than 20 levels deep. UNIDEPTH, on the other hand, works all the way to Excel's current maximum depth (64). I am not sure why this happens, but until it is fixed in a future version, I will keep them separate and document the limitation.

For now, I will keep it half baked.

I agree, it sounds unhelpful. I actually tried changing it to MAXDEPTH, but then hit this:

The function breaks if the nesting level is more than 20 in the non-linear search, probably due to Excel’s auto-recursion limits. I can’t confirm it, since Excel recursion limits have always been ambiguous to me.

What I do know: as long as depth is 20 or less, it works regardless of array size.

Excel has been returning unexpected errors lately, sometimes #VALUE! inside IFERROR, or volatility behavior from non-volatile functions. That makes me less confident that this is purely a recursion limit; it could be related to an Excel bug or unstable calculation behavior. But the linear approach works up to 64, (Excel’s maximum nesting depth)

/*
Name: MakeNestArr
Description: Creates a uniform-nested array given size and element depth. 
Example: = MakeNestArr(2,1,3) -> {{{0}};{{0}}}
[element_depth]: Controls how many nested-array levels are created. Omitted defaults to 2.
*/

MakeNestArr = LAMBDA(rows, columns, [element_depth],
    MAKEARRAY(
        rows, columns,
        LAMBDA([r], [c],
            REDUCE(
                0,
                SEQUENCE(IFOMITTED(element_depth, 2) - 1),
                LAMBDA(acc, [nxt], {acc})
            )
        )
    )
);

Actual test:

=ARRDEPTH(MakeNestArray(100,100,20),TRUE) // works

=ARRDEPTH(MakeNestArray(1,1,21),TRUE) // retunrs #VALUE!

=ARRDEPTH(MakeNestArray(1,1,64)) // works

If we make MAXDEPTH the default, it will always break for arrays deeper than 20. I know 20-level-deep arrays are unrealistic in practice, but do you still agree with changing it to MAXDEPTH?

As for your scenario of:

 how many times to apply FLATTEN

I’m assuming you want to completely flatten any array. I haven’t tested much, but I think FLATTEN internally uses recursion and stops once all arrays are flat. So prematurely setting [levels] to 64 on a 2-level array shouldn’t hurt performance much:

=BENCHMARK(LAMBDA(FLATTEN(MakeNestArray(50,50,21),,64))) // took 140ms

=BENCHMARK(LAMBDA(FLATTEN(MakeNestArray(50,50,21),,21))) // took 130ms

On my side the difference was almost negligible. Perhaps you could add something like this instead:

FLATTENALL = LAMBDA(array, [pad_with], FLATTEN(array, pad_with, 64));

PeterBartholomew1​ 
Thanks for the feedback. Regarding the default value of is_ragged, I first had to decide whether “depth of a ragged array” even makes sense.

In languages with native nested-array support, such as Python/NumPy, checking the dimensions of a ragged array does not really make sense. There is no single depth because different branches can have different nesting levels. Technically, the correct behavior would be to return an error.

Interestingly, Python’s native depth function can incorrectly return 1 for a ragged array, regardless of its actual nesting. Recent NumPy versions also raise an error rather than silently creating an object-dtype array.

So the options were:

1. Return a hardcoded value: misleading and not Excel-like.
2. Return an error: technically correct and industry-standard, but not user-friendly.
3. Return the maximum depth: useful, but for people with python background it implies the array is uniform, which may not be true.

Both extremes felt too strong, so I chose a middle ground: calculate maximum depth only when the user explicitly sets is_ragged to TRUE.

If maximum-depth scanning were the default, the function should probably be renamed MAXDEPTH, since it would answer a different question. I was also unsure about the extra calculation cost, especially with deeply nested arrays and Excel’s recursion limits.

I did not want to force a non-industry-standard default for ragged arrays. A design choice had to be made, and I am happy to change it if the overhead is minimal and people prefer maximum depth as the default.

I see your point. In your view, is the extra calculation cost acceptable for making maximum depth the default?

As for the # spill indicator it's confirmed it adds an additional nesting level identical to using { }, confirmed using ARRAYTOTEXT:

={SEQUENCE(5)}

=ARRDEPTH({E3#},TRUE)   //3

=ARRAYTOTEXT({E3#},1) // {{{1;2;3;4;5}}}