Forum Discussion
Return of the Corkscrew with Spilled Arrays
James_Buist Just for good measure, MMULT can also get the job done with minimal effort.
Closing Balance (cell G22):
=LET( data, HSTACK(F22:F24,G12:M14+G17:M19), cId, SEQUENCE(,COLUMNS(data)), arr, MMULT(--data,--(DROP(cId,,1)>=TOCOL(cId))), VSTACK(arr,BYCOL(arr,SUM)) )Cheers!
Good stuff! I'm glad to hear you found an application for the broadcasting method. Reception so far has been mixed... some love it and some don't. Most users tend to stick with what's familiar and avoid straying outside their comfort zone.
MMULT works well in many different situations, but it's not without its drawbacks. For starters, it can be difficult to get the x and y axis in the right direction. I've also noticed it has a tendency to fail with the 'Excel ran out of resources' error when working with larger datasets, whereas the newer dynamic array helper functions will finish processing the results, even though they are typically slower.
On a side note, data structure can make or break any spreadsheet. A good structure (normalized data in related tables) can be easy to query, summarize and output as needed, whereas a poor structure (e.g. cross-tabulated data) will usually require more advanced methods to manipulate. Many of these questions/scenarios could potentially be avoided simply by organizing your data in a better structure (although that's just speculation based on the skeleton sample data provided at each turn). 😉
I agree. I am trying to build a new version of an older type of best practices financial model using DAs through out. And they can enable one to skip a few steps and rather than having three sections for each key entry one can reduce it to one. But also, I debated for ages on the handling of multiple entities - for smaller organisations - and decided that there was more flexibility in combining the entities into a single group and doing each operation on the group as a whole rather than having separate sheets for each entity. Often easier to group by part - Revenue, Overheads, Working Capital calcs separated by sheet and do all entities in each group. This way one can just expand the group zones as required. Consolidations by entity have been more of a challenge and those you have hugely helped me with, But input sections and line item processing can be independent of entity and just needs an entity code to enable aggregation or consolidation. As you will have seen from my samples. Clearly I am just giving a small subset of the model to demonstrate the requirements for each post. The flow from inputs to middle tier calcs and the final outputs is one directional unless especially required. ie taking a feed from an output back to a mid tier is avoided even if it may seem more convenient. I hate bi directional references; sheet refs should flow in one direction only between sheets and typically top to bottom on sheets.
However, the grouping of entities has definitely posed a number of additional challenges as often a single row is less complex to calculate using a DA than a full 2D array. At least for me at my level. But I do like the challenge and have and am learning a huge amount from it