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

Question

Hey everyone!I’ve been diving deep into Excel’s new nested arrays features lately, and I absolutely love the flexibility they bring to data structuring. However, managing them can get a bit tricky, so I figured I’d build some specialized tools to handle them seamlessly.Public Interfaces &amp; Arguments=ISRAGGED(array)ArgumentDescriptionDefault BehaviorarrayTarget nested structure to validate.Required&nbsp;=ARRDEPTH(array, [is_ragged])ArgumentDescriptionDefault BehaviorarrayTarget nested structure to measure.Required[is_ragged]TRUE: Scans all branches for the deepest path. FALSE: Speed test on the first path only.FALSE (Omitted)TL;DR (What makes it tick):Function NameTypeWhat It DoesARRDEPTHPublic UIReturns nesting depth. Supports deep checking or fast linear testing.ISRAGGEDPublic UIStructural validator. Returns TRUE if array branches have uneven nesting.ANALYZE_NESTINGHidden Core EngineMulti-process recursive parser that tracks tree structure geometries.LINEARDEPTHHidden UtilityHigh-speed depth checker that evaluates the first element path exclusively.You can get both functions from my GitHub Gist: https://gist.github.com/Medohh2120/9cdf939036942c9672c57ccd6de696d3

peterbartholomew1 · Answer

Medohh2120​&nbsp;I agree, it can be quite difficult to keep a mental map of the number of levels of nesting one needs to navigate.&nbsp; 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.&nbsp; Interestingly treating the single cell as the potential anchor to a spill range increased the depth count.&nbsp;&nbsp;&nbsp;

medohh2120 · Answer

PeterBartholomew1​&nbsp;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:Return a hardcoded value: misleading and not Excel-like.Return an error: technically correct and industry-standard, but not user-friendly.Return the maximum depth: useful, but for people with python background it implies the array is uniform, which may not be true.&nbsp;Both extremes felt too strong, so I chose a middle ground: calculate maximum depth only when the user explicitly sets is_ragged to TRUE.&nbsp;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.&nbsp;I see your point. In your view, is the extra calculation cost acceptable for making maximum depth the default?&nbsp;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}}}&nbsp;

peterbartholomew1 · Answer

I lack your experience of what to expect of other languages (my Fortran experience from many years ago is hardly relevant).&nbsp; I think the list of keyword/entity pairs that I applied your functions to is inherently ragged so returning 1 did not help me much, since it characterises the keyword rather than the entity depths.&nbsp; I guess it was MAXDEPTH that I had in mind, as one of my requirements was to know how many times to apply FLATTEN.&nbsp; My concern was that returning a value of 1 was positively misleading.

Forum Discussion

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

Public Interfaces & Arguments

TL;DR (What makes it tick):

3 Replies

Argument	Description	Default Behavior
array	Target nested structure to measure.	Required
[is_ragged]	TRUE: Scans all branches for the deepest path. FALSE: Speed test on the first path only.	FALSE (Omitted)

Function Name	Type	What It Does
ARRDEPTH	Public UI	Returns nesting depth. Supports deep checking or fast linear testing.
ISRAGGED	Public UI	Structural validator. Returns TRUE if array branches have uneven nesting.
ANALYZE_NESTING	Hidden Core Engine	Multi-process recursive parser that tracks tree structure geometries.
LINEARDEPTH	Hidden Utility	High-speed depth checker that evaluates the first element path exclusively.