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

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));