Forum Discussion
Is now the time to abandon the concept of relative referencing?
I think this is a good idea for an option that could be enabled in Options | Formulas. I approach a given sheet with the mindset of how can I get it done in 1 move with a dynamic array? I'm totally on board with Excel 365 as an array-based powerhouse capable of so much more than the scalar-based legacy versions of the past. It's fun to use Excel to solve linear programming puzzles! There's a certain art to authoring elegant solutions in Excel that has really come on in the last couple years that I enjoy.
I think there's still a certain tendency to underestimate what Excel 365 is capable of and make a bee line for vba solutions. LET/LAMBDA fundamentally changed how formulas can be authored but it's very much a takes one to know one club at present. One doesn't often stumble into LET/LAMBDA by accident. I don't mean to be a gatekeeper but it's usually something someone takes up after years of study. For this reason, I think the proposed change could be added as an option to be enabled but defaulting it might cause a stir!
"defaulting it might cause a stir"
Yes, but that is at the core of the argument. Users will follow the path that is most easily discovered. For me, that path leads firmly in the wrong direction, enticing users to follow. The scalar operations do lead to a very direct sense of the user manually interacting with their numbers. That suits many users, but they should receive some warning/indication that they are opting for legacy methods that deliver little in the context of modern Excel.
They will come to think of being limited to scalar arithmetic as representing 'simplicity' yet, in reality, the inherent complexity of the problem has simply moved to the task of wading through a sea of semantically limited cell formulas trying to divine the path of the overall calculation. Such users may seek more knowledge from the 'tips and tricks' genre of multiply plagiarised content, perhaps thinking that memorising over 350 shortcuts will in some mysterious way add wisdom to the proceedings.
With dynamic arrays, I look for 'simplicity' not in the individual scalar operations but in the overall patterns of array calculation. Operations like sorting, filtering, wrapping, grouping emerge along with the concept of arrays; they are not meaningful at the level of scalar thinking. Many problems, such as that of banded bonuses given to salesmen or taxation bands (attached) have array solutions that follow a different path from that adopted by fill-down formulas.
This process of abstraction, in which the details of a calculation are left to Excel, is a key element in the fight to control the error rates that characterise spreadsheet solutions and give the spreadsheet such a poor reputation. Overall, I would not suggest attempting to force users to adopt modern methods (that would simply drive many casual users away) but neither do I want to signpost a default route that ultimately leads to a dead end.
PeterBartholomew1 My two cents...
I'm more of the attitude that if the situation calls for it, use it. If not, just keep it simple. This is especially true when helping out beginners. If I were to hazard a guess, I would say that the "average" user falls into this category. Intermediate users would have a decent chance at adapting, but people who are still using =SUM(A2+A3) formulas are not very likely to grasp major concept changes. I still feel the "default" behavior should be the path of least resistance, so no one gets left behind.
On the topic of defining names, I say each to their own. For me, less is more (in general). I find spreadsheets that were created with excessive, unnecessary named ranges to be harder to modify, even if I was the one who built it. Too much time is wasted jumping back and forth between the worksheet formulas and Name Manager trying to reverse engineer everything. I would much rather see the cell references directly in the formula, so I know exactly what they're referring to. Having said that, there are plenty of situations where named ranges are essential, and the majority of my spreadsheets have at least a few.
Further to that, when it comes to defining names within the LET function, I'm all for defining each variable to improve readability, especially when first introducing others to LET. However, it's easy to get carried away here too. One thing I'll probably never get on board with is defining the LAMBDA function first for BYROW, BYCOL, MAP, REDUCE, SCAN, etc... it's like putting the cart before the horse, and actually reduces readability in my opinion, as you end up having to backtrack to figure out what's happening, rather than just reading each step in logical order.
I often find myself suggesting solutions on this forum that I would never use personally, simply because I would never setup my spreadsheets in a manner that calls for them. Rather than trying to explain the pitfalls of certain design choices, I just end up answering the question directly, even if it goes against my own preferred methods. The beauty of Excel is that there are many ways to get the job done. Some methods are easier to understand, while others can be more efficient. In many cases, though, it boils down to personal preference.
By the way, the custom TaxCalcλ function you shared in the TaxBands file is awesome. It's so simple and yet beyond the comprehension of any normal person (looks like Thunks was used?). You sir, are in a league of your own. 🙂
If you liked TaxCalcλ, try its big brother
= FIFO.GenerateTableλ()As it stands, the lack of any parameters would cause it to error but following Craig Hatmaker 's ideas on Component-Based Software Engineering, eusprig-proceedings-2023.pdf (pp 33-58), it actually returns
With the parameters correctly input and with option=2, the result might be of the form
[shows costs pivoted by output (rows) and input (columns)]
I have also used the same formula for a challenge posted by Ian Hewitt on Chandoo a few year back with involved the Fe content of ore mined and supplied on a FIFO basis.
At its core, the idea of the calculation is the same as that used for the tax bands. That is stack two arrays including cumulative quantities and sort in ascending order. Then take the difference to assign quantities to individual tranches.
Thanks for the shout out. Those functions look great so I guess it's time to package your LAMBDAs into 5g libraries.
BTW - 5g is on fire. In December I presented 5g at the University of Arizona's MECC championship. Last month Danielle Stein Fairhurst presented 5g functions a the Excel Global Summit. Model Citizn just posted an article on 5g. All next week I have 5g workshops for Full Stack Modeller. In April I'm at the University of Tennessee. In May I'm presenting at FMI (Financial Modelling Institute). Finomatic Consultancy is converting their models to 5g... and this all traces back to when you introduced me to your work with array formulas, what, 10 years ago? Thanks for all your help Peter.
I guess it all depends on how one defines 'simplicity'. Whereas others see simplicity in a mass of disconnected formulas because they individually involve no more than primary school arithmetic, I regard that to be an unstructured and complex mess of relationships. Simplicity, for me, is achieved when many disconnected observations can be derived from one guiding principle (in this situation, a single array formula). I accept that it is me that is weird in this respect, an abstract thinker that studied mathematics, theoretical physics and astronomy (over half a century ago) before going on to work as a research scientist in aeronautics. There was a time when I had probably written more matrix formulas than natural language sentences!
Spreadsheets are more about doing than thinking. I wonder how many spreadsheet users could offer a coherent explanation of what is achieved by relative referencing and why the same formula, placed in different locations on the worksheet, give different answers. I haven't used relative references since 2015 , 1704.01142.pdf (eusprig.org), and by now things that spreadsheet users find normal, I see as odd. When I look at a term in a formula, I do not need to know 'where did that come from?'; I want to know 'what does that represent?' The A1 notation tells me the wrong thing; a name just might tell be what I need to know to evaluate the formula.
One of the hardest things I find, is to persuade an experienced Excel user to stop thinking in terms of 'and fill down' and see instead, a single array which may be reordered, reshaped or aggregated to achieve the desired results. There are times when it would be easier working with a complete novice who is free from the expectations of traditional spreadsheeting. Interestingly, though, this far the responses have been from individuals who are comfortable with traditional spreadsheeting but, at the same time, have fully mastered the opportunities presented by the new. You clearly beat me on versatility!
It's interesting you mention defined names which have been used more since the advent of LET/LAMBDA. I agree that sometimes there can be too many defined names and it becomes difficult to manage.
There's a long forgotten feature that was removed with Excel 2007 because it was seldom used: Natural Language Formulas.
This is from the wayback machine:
Microsoft Excel 2007 (nee Excel 12) : Deprecated features for Excel 2007 (archive.org)
Natural Language Formulas
This feature allowed people to use the labels of columns and rows on worksheets to refer to the cells adjacent to those cells without explicitly defining them as names. This feature has been disabled by default since Excel 2000 based on customer feedback. In Excel 12 we will completely remove this seldom-used feature from the product. When opening files in Excel 2007 that use this feature, formulas will be converted to use cell references.
Note: This feature could be found in the Excel 11 options dialog with the title “Accept labels in formulas”
Insert Name Label
This command allowed people to more explicitly specify labels for Natural Language Formulas and is being removed as part of that feature.
Maybe I'm misremembering this feature but it sounds like this feature would be a lot more useful in 2024 than in 2003.
Interesting. I have seen add-ins along those lines but I didn't know Microsoft had ever tried to change end-user perception in that way. The spreadsheet user base appears to be very resistant to supposed 'improved' practice!
For me, it was always to opposite; I would simply 'glaze over' at formulas such as
= SUMIFS(Trading!DK5:DK12025, Trading!DI5:DI12025, "banana", Trading!DJ5:DJ12025, "Japan")I don't think I even read the references after the first few; it was just encrypted 'stuff' that I couldn't be bothered to think about. My only strategy for maintaining attention was to replace the direct references by names derived from any adjacent annotation (assuming there was any). That way I might get to see
= SUMIFS(revenue, country, "banana", product, "Japan")at which point even I might begin to suspect that all is not right with the formula. By the time I had understood the workbook, I had completely refactored it and then had the embarrassment of confessing to the author that I no longer had a copy of their workbook but that I had found some errors along the way!
- I think if a setting like this became the new default there would undoubtedly have to be other advancements within Excel. Conditional Formatting and Data Validation still think in terms of scalars and the formula bar is long overdue for an upgrade.
To get the average user to think in terms of arrays instead of scalars, the application must be consistent across features like those mentioned above. I've taught Excel within my organization for nearly 15 years. My students are newer employees who often don't have any previous knowledge of the application. The good thing is they start out learning Excel 365, how to spill, how to think in terms of arrays, etc. from the start. The challenge for me is getting them to understand spilling yet pumping the brakes for something like conditional formatting with a formula, for example.
Excel has come a long way since 2018.Sadly, conditional formatting is full of gimmicks (admittedly, some rather neat) but the basics are antiquated. All I need is to be able to do is apply formatting based upon an array formula to its spill range, given a user-specified anchor cell. The formula should be capable of including range intersection operators. In other words, all I require is what is missing!
It is my understanding, though, that the CF code base is so old that it would require a massive effort to bring it into the 21st century.
