Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 10, 2026

Medohh2120​ 

I agree, it can be quite difficult to keep a mental map of the number of levels of nesting one needs to navigate.  It is nice that Excel will automatically nest arrays of arrays and conversely automatically flatten an isolated leaf node, but it does make keeping track that little bit harder.

I wonder whether the default setting for [is_ragged] should be TRUE (that is to guarantee a correct result even if additional calculation is required?

= ARRDEPTH(fullyNested, TRUE)       // Returns 3
whilst
= ARRDEPTH(fullyNested#, TRUE)      // Returns 4

= ISRAGGED(fullyNested)             // Returns TRUE
= ARRDEPTH(fullyNested, FALSE)      // Returns 1 (which is not helpful/)

I tested your function against a copy of a worksheet formula that was wrapped in a further nesting step to reduce it to a single cell.  Interestingly treating the single cell as the potential anchor to a spill range increased the depth count.

 

 

 

Medohh2120's avatar
Medohh2120
Tin Contributor
Oct 10, 2026

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}}}