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...
djclements
Mar 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. 🙂
Patrick2788
Mar 04, 2024Silver Contributor
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.
- PeterBartholomew1Mar 06, 2024Silver Contributor
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!