Forum Discussion

tboulden's avatar
tboulden
Iron Contributor
Mar 09, 2021
Yes, I might revisit with a less naive approach, and special cases for LET/LAMBDA formulas; it really depends on what you find helps you understand the formula. I sometimes forget how far in I am when visually inspecting a formula by counting brackets. And the UnpackLet outputs were a perfect test case: they return valid/accurate results, but its formula-stew if ever I saw it.
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 10, 2021

tboulden 

I love the term 'formula stew'.  I have decomposed structured formulas manually on occasion, just to see what I get.  It usually generates a mess but not always. 

 

Simplicity, like beauty, is very much in the eye of the beholder.  For many years now, I have settled on array formulas which, in turn, makes it easier to use defined names.  In fact Mike Girvin commented

"As for your COUNTIFS, I have never, in almost 3 decades, seen a spreadsheet like yours with ALL the calculations entered as arrays!!! Now I see why your are so excited by the new Excel Calculation Engine."

 

Earlier, before I had fully-committed to the pain of CSE, I had exchanged models with F1F9 (financial modelling company prominent in promoting the FAST Standard).  One of the sheets was returned with a 'simplified version', i.e. no array formulas, no names.  The question then becomes:

In what way do you believe that replacing 4 names and 2 formulas, aggregating data over 100 cells, with a (direct referencing) system that introduces 17 billion names (only 100 of which point to occupied cells and 50 individual formulas, represents 'simplification?

 

I have been known to argue the case that 'complexity' is no more than 'simplicity' in excessive quantity.  I would suggest that is only through the use of more abstract concepts that 'simplicity' is regained. 

  • tboulden's avatar
    tboulden
    Iron Contributor
    Mar 10, 2021
    I'm enjoying learning more about array formulas and dynamic arrays through my LET/LAMBDA experimentation; I may try an experiment like you say where all calcs are arrays, I know my coworkers would "love" it. I agree, 4 names and 2 formulas is much less worry if they are well-written, well-explained with comments or documentation, and well-protected so Joe Schmoe can't break it accidentally.

Resources