I have gone back to my Kindle and discovered that I actually have a copy of PowerPivot by Ferrari and Russo I received as a gift. Since I have Office 2010 and, at the time, did not have the Power tools installed, I found it very heavy going! For me 'data' is half a dozen values that I put into a model to act as controls, so I wasn't even in the starting gate.
I was left with a feeling of Schizophrenia. On one hand, I was being told 'please don't use names, and even worse array formulas, out poor little darlings (their clients) won't understand them. The next moment reading convoluted descriptions of such complexity that I seriously wondered who on this planet was meant to understand what was going on!
That said, the features of Excel that you so articulately describe are exactly those that I find abhorrent. I hate the fact that Excel formulas are placed within a cell (and hence are many-to-one in terms of references) rather than being applied to a multi-cell range. Most business objects of any significance are either functions of time (say), represented by arrays, or lists, stored as tables with related attributes. Any significant operations are likely to apply at the level of the arrays or lists and it should be for Excel to carry the implementation down to cell level. After all, I can almost guaranty that I will create a multitude of errors if I attempt repetitive implementation tasks.
For me Dan Bricklin's most annoying decision was to recognise that variables could be named the programmer's way but instead opt for the second-rate town plan mapping stratagem that did not require the 'meaning' to be declared because to require the user to describe their data would be 'tedious'!
