Forum Discussion
Excel large, n=2 and n=3 return the same value.
Look, with millions of users who don't want or can't afford to buy new computers and licenses we always have to find some compromise. Excel is the behemoth in software world with it's own rules of behaviour.
I am not suggesting that others should follow the same path. I came to dislike the idea of direct cell referencing in principle since, to me, the location of a value or formula on a sheet has no bearing as to its significance. I had gravitated to using named ranges and CSE array formulas most of the time despite the fact that Excel was heavily optimised for a far more interactive way of working.
Now with DA I am free to think and work in a manner that has always been more natural for me; after all, my day job was entirely built on 'matrix methods'. Equally, I accept that many of the financial modellers I meet prefer to copy individual values repeatedly so that they are close to the each formula that references them; parts of the Standard are 'Do not use Names' and 'Do not use Array Formulas'.
They are perfectly free to go their way, whilst I (thankfully) depart in an entirely different direction. There are many 'gurus' that can support traditional working but few, like you, that can do both.
You are right. But sometimes it's quite hard to shift from my own environment with LET(), names, etc to shared environment. I always have to think is this or that available to other people, and, even if available, have they any glue about that.
Another point, having some patterns collected for years, not always think that it could be done another way using new possibilities. Such communications help a lot.
Finally, we are all human being and do lot of mistakes. One more, one less, but we do. Again, forum helps to learn on my own on other people mistakes.
I confess, there have been times when I wonder whether I am helping or causing more confusion when I post a solution that I know will appear totally alien to almost all Excel users. In addition, I do not think I have reached a balanced view concerning the limits of how deep a LET formula should be taken.
I think I had a similar dilemma when I first realised that Named formulas could be nested one within the other several deep. It was both liberating (array formulas without the CSE) but, at the same time, it didn't seem entirely right to strike out on such a lone path (even if it did enable me to translate numerical currency values into natural language (I tried English and German). It worked fine, but it was even questionable whether MS had any obligation to support such use.
I did get around to publishing some initial thoughts on dynamic arrays and presented the paper last year. Since then, Charles Williams, whom I assume you know, included ACCUMULATE in FastExcel.
Hogstad_Raadgivning If this exchange has moved too far from your initial question, do not hesitate to bring the discussion back on topic.
PeterBartholomew1 , that's a great paper. Can't say I fully agree with everything, but hope I catch your point.