Forum Discussion
LAMBDA: PrettyPrint
Wow! That is crazy. I would never have thought of using an Excel formula to format Excel formulas. The later steps of regrouping array constants and inner parentheses is a key element of providing structure; little is gained by turning a horizontal mess into a vertical format, even with the indentation. When laying formulas out by hand, I tend to try to have the complexity of any given line grow in proportion to the growth in the number of lines.
I did try to see what happens when I applied the pretty print function to its own definition but a LET function gains little because of the structure that has already been built into the formula manually.
I haven't got as far as looking why applying the formula in different ways gives different results. I simply haven't got to grips with the formulas to the required depth.
- 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.
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.
- 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.
