Forum Discussion
PeterBartholomew1
Mar 03, 2024Silver Contributor
Is now the time to abandon the concept of relative referencing?
The default action when one clicks a cell whilst inputting a formula is for Excel to return a relative reference. That allows a user of legacy Excel to address the members of a list or array by drag...
Patrick2788
Mar 03, 2024Silver Contributor
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!
- Craig HatmakerMar 09, 2024Iron Contributor
Patrick2788
"defaulting it might cause a stir"
Please take this in the spirit of humor. Your point is very valid, but a bit ironic given the topic and how enabling dynamic arrays required a major change to Excel's calculation engine default behavior.- Patrick2788Mar 09, 2024Silver Contributor
Thank you for taking the time to read my reply and respond.
I do believe the introduction of dynamic arrays marked a turning point in the history of Excel - a major change in the calculation engine for the better. I can only speak for myself. Colleagues of mine that have used Excel since the 80s and 90 have not completely bought into Excel-based-on-arrays for lack of time to learn new skills, being content in one's current skill set, not being interested, etc. - whatever the reason may be. I still hear of columns being re-arranged to accommodate the limits of a base VLOOKUP, Advanced Filter being used instead of UNIQUE, helper columns used instead of LET, and plenty of other examples. The awareness of functions like FILTER, XMATCH, LET, CHOOSECOLS, CHOOSEROWS, TAKE, DROP is close to 0.
Clients are usually very open to dynamic arrays. Lambdas are usually received very well. Though I still have to talk people off the VBA ledge. There's a certain mindset that if someone doesn't know how to do it in the application that VBA must be the answer. I think anyone beginning their studies of Excel in 2024 would be wise to find a course specifically geared towards Excel 365 and stay away from macros!
- PeterBartholomew1Mar 09, 2024Silver Contributor
"a course specifically geared towards Excel 365"
Have you identified such a course? In some ways that is what this discussion was intended to cover. It is always possible to bolt Dynamic Arrays, LET and LAMBDA on to an existing course as 'new', 'advanced' topics. Then one might need to debate whether Lambda should wait until Power Query and Pivot Tables have been covered.
What I would argue for is that DA, LET and LAMBDA should not be left to the final steps of training as advanced topics but, rather, treated up front as the basis of modern Excel; it is traditional spreadsheet techniques that should be left to the end for those who need to address issues of backward compatibility.
- PeterBartholomew1Mar 03, 2024Silver Contributor
"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.
- djclementsMar 04, 2024Bronze Contributor
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. 🙂
- PeterBartholomew1Mar 06, 2024Silver Contributor
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.
- Patrick2788Mar 04, 2024Silver ContributorI 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.- PeterBartholomew1Mar 05, 2024Silver Contributor
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.