MicrosoftI believe you are correct in your identification of nested arrays as rich data types but I do not think there are any plans to support the dot notation for attribute values (I did experiment with .name and .value but merely established that is not a supported exploitation route).
I notice your test example start as a formula containing a valid array constant. Most workbooks do not contain formulas, never mind array constants! Some of the confused messaging you have encountered results from an effort to provide value for all levels of usage. A basic user (far the most numerous category) might input a comma separated list 5,6,7 into a cell, as a compact way of storing 3 values. Now the ▤ command (or Ctrl/J) will convert that to a nested list which is at least a first step towards being able to sum it or flatten it for inspection. I do find it slightly confusing that the list is vertical or that 5,6;7,8 is not supported as a 2D array but, frankly, it is better that you and I are slightly confused than having basic users face something they find incomprehensible.
I like your analysis of the "container" and "implicit-expansion" views. I think both are valid but, in many instances, one view is meaningful and the other not. For functions such as MAP the assumption must be that the nesting was introduced for a purpose so MAP will respect the container structure and operate upon each contained entity in turn. On the other hand SUM expects type 1 data and the decision was made to cascade the calculation down to leaf nodes where such values might exist.
That is not always the best decision, a nested Table, for example, might contain dates, quantities and prices. Summing those would not be great. There BYROW could take one through the nested records and the LAMBDA function set up to process each record correctly. A clear statement of the rationalle would be valuable but, to date, I have found the new functionality eminently usable (even tempting me to renounce the thunks I have used hitherto).
Talking of Tables, a nice trick is to use GROUPBY to break a monolithic table into an array of nested sub-tables that can be looked up individually by their group's name.
Thanks for the thoughts, PeterBartholomew1. As you rightly note, MS has always faced a challenge designing for users at all points of the competency spectrum (while also often maintaining backward compatibility to the mid-1990s).
I do find it slightly confusing that the list is vertical or that 5,6;7,8 is not supported as a 2D array
That's not showing for you? It looks great on my end - `={{5,6;7,8}}` converts to a 2x2 array.
I wonder if we could have the best of both worlds thanks to the FLATTEN function. Arrays could be first-class objects (containers), unless the caller first passes the array through FLATTEN(). That would expose the inner elements of the array -- i.e., explicit expansion.
I'd rather force the user to use FLATTEN than to have inconsistent, mysterious behaviors.
I explored FLATTEN() a bit. It's pretty good -- especially thanks to the `levels` argument -- but it still has quirks because of vertical and horizontal data. I'll use the myHList and myVList examples from my earlier post.
={"txt", 123, {5,6,7}, {9;10}, 123}
={"txt"; 123; {5,6,7}; {9;10}; 123}One might think that FLATTEN would work the same on both of these. E.g., if I used COUNTA, I would expect 8 elements. Now, I understand how mixing vertical and horizontal list/arrays can result in >8 elements because of padding #N/A values; not ideal, but I get it. BUT, logical as it may be "under-the-hood," it's surprising that these two FLATTEN functions don't return the same number of items.
=COUNTA(FLATTEN(myHList)) // returns 14
=COUNTA(FLATTEN(myVList)) // returns 18To me, flatten means "unpack inner iterables into the main iterable's level," but that's not how it's being implemented here.
Maybe you would be more comfortable with
= SUM(MAP(myHList, COUNTA)) // returns 8
= SUM(MAP(myVList, COUNTA)) // returns 8That focusses upon the inner arrays and bypasses the padding.
You might like to guess what to expect from
= {myHList, myVList; myVList, myHList}
or
= FLATTEN({myHList, myVList; myVList, myHList}, "", 2)
Note: The step I didn't like was simply typing 5,6;7,8 into a cell and then using the ▤ command (or Ctrl/J). This gives
which is improbable as the intended result.
Maybe I need an alternative ▦ (to array) command button!
Or perhaps a fallback mode for ▤ that says "if you insist on providing a valid arrangement of row and column separators, I will, with great reluctance, accept that you just might know what you are doing, and return a 2D array as requested".
MicrosoftThank you B_D_T and PeterBartholomew1 for your brilliant responses on this thread already!
I just had one thing to clarify: from the perspective of Calc, you can entirely ignore the concept of 'Lists' - it's just a special name for the subset of arrays in cells that are Nx1 (vertical) that don't contain nested values.
The distinct naming is just to provide the simplest vehicle possible for putting multiple values into a cell. That's also what rationalized the simplified display and editing experiences.
Thanks JakeArmstrong!
it's just a special name for the subset of arrays in cells that are Nx1 (vertical) that don't contain nested values.
So when I'm teaching this, I should use the term "array" regardless of shape? I only ask since the term "list" displays consistently for any Nx1 array, even those with nested value. E.g., the following all say "List with 2 items." I just want to make sure I have my terminology correct!
={{1;2}}
={{1;{2;3}}}
={{1;{2,3}}}B_D_T they are all absolutely arrays in cells - Lists are a subset. We expect the term 'List' to be used broadly, so it might make sense to mention it, but their distinction should be ignored when talking about calculation.
Of your three formulas, all three make arrays in cells. The first can also be considered a List.
The confounding factor here is that they all use the same icon (▤). We've gotten lots of feedback that this is confusing, so we will be updating Nested Arrays like (2) and (3) to use a different icon. That way, in the future, ▤ == List. In the meantime, unfortunately since the icon reflects the outer dimensions, some Nested Arrays look like Lists.