Forum Discussion
Excel Nested Arrays: Find the Nesting Depth of Uniform and Ragged Arrays ARRDEPTH() & ISRAGGED()
For ragged arrays, a single depth value is inherently ambiguous. I would make the function return both the minimum and maximum leaf depth, for example {MINDEPTH, MAXDEPTH}. A uniform array then returns the same number twice, while a ragged result immediately shows the range.
That also avoids making is_ragged a behavioral switch. ISRAGGED can simply test whether the two values differ. If ARRDEPTH must stay scalar, an optional mode such as "MIN", "MAX", or "STRICT" is clearer than TRUE/FALSE; STRICT could return #N/A when leaf depths differ. This makes the default predictable and removes the need to guess what "depth" means for an irregular structure.
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.