Forum Discussion
Is now the time to abandon the concept of relative referencing?
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.
PeterBartholomew1 That’s pretty cool too. There’s a lot more to it than TaxCalcλ, so I’ll have to really break it down and see if I can understand what’s going on.
In reference to your SUMIFS example, yes, the named ranges quickly reveal the error. That’s the type of situation where a structured table is the way to go. Admittedly, SUMIFS has always been one of my favorite Excel functions, and even more so now with MS365 as it can easily spill a 2D array of results when using both horizontal and vertical arrays as the criteria. Performance was sometimes an issue with SUMIFS in older versions of Excel, but not so much anymore (unless you’re dealing with huge amounts of data, in which case PQ can be used to reduce the dataset accordingly).
That’s very interesting to hear a bit about your background. I figured it was something technical, but had you pegged as being an ex-computer programmer or engineer of sorts. My background is considerably less impressive, lol. I studied ornamental horticulture, majoring in turfgrass management, but found it difficult to make a living in Canada, as the golf season is only 5 and a half to 6 months long! After a few years of that, I put my math and computer skills to use and found a more stable job as a data entry clerk at an accounting office, which is where I developed my love for Excel. John Walkenbach’s books were a great resource at the time, and really opened my eyes to what Excel was capable of.
I made the jump from Office 2010 to MS365 around 2 years ago out of necessity. Personally, Office 2010 had everything I needed, but clients I previously built spreadsheets for were migrating from Sage 50 to Xero and needed integration with newer add-ins only available in MS365. The first spilled array formula I encountered was when the SCOTT.CHART function was released in mid-late 2022 (as part of Scott’s Add-Ins for Xero) and my initial reaction was “meh… a table would be better”. It intrigued me, though, and after a bit of research online, as well as watching a few YouTube videos, I saw the potential, but still didn’t have very many real-world scenarios to play around with. Joining the discussion here in October 2023 really helped me out in that department, and it’s been a great place to learn, experiment and develop a better understanding of the new dynamic array functions.
Tying all of that back to the topic at hand, I think part of the reason why array functions come naturally for people like you and me, as well as the other contributors here, is the way our minds work. There’s a certain amount of logic required... you need the ability to visualize what the results of an array will look like in order to build a complex, single-cell formula (especially when dealing with nested arrays). This can be learned and achieved through experience but will inevitably come more easily for a small percentage of users. That’s not to say relative vs absolute vs mixed references aren’t a source of confusion… they absolutely are. But I think they’re still easier to learn and understand for the average user. 😉
