Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor
Apr 30, 2022

Formulas Challenge for 365

Thank you for reading this post!   I propose a formulas challenge (The shorter the better).  Attached is a workbook in need of two formulas.  I have solutions for each (Formula 1 is 66 characters, ...
excel
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
May 03, 2022
Admittedly, the shortest formula approach may not be the best if taking into account sustainability and being able to use the formula in more workbooks. I've been looking for situations to create universal LAMBDAs that I can use again and again. I've gone back to some old vba exercises to see if I could make LAMBDAs do the same thing as vba. I've run into the dreaded 'array of arrays' limitation (TEXTSPLIT, naturally) when trying to write a LAMBDA to un-pivot some data. I believe it's possible with INDEX (and maybe OFFSET) but not worth the trouble if PowerQuery can do it in a snap.
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 03, 2022

Patrick2788 I am not happy with the array of array limitations but Thunks can be used to overcome the problem in many instances.  The attached workbook contains a Lambda function 

= Expandλ(arrayϑ)

which turns a column of row-thunks back into a 2D array.  I would far prefer such tricks were not necessary but it is something I have been bitching about for years (along with array breakup that is now solved by Lambda helper functions).

Expandλ = LAMBDA(ϑ,
    LET(
        n₁, ROWS(ϑ),
        n₂, MAX(MAP(ϑ,Columnsλ)),
        MAKEARRAY(n₁,n₂,
            LAMBDA(i₁,i₂,
                INDEX(INDEX(ϑ,i₁,1)(),i₂)
            )
        )
    )
);

Columnsλ = LAMBDA(xϑ,COLUMNS(xϑ()));

Thunkλ = LAMBDA(x, LAMBDA(x));

Microsoft Word - Will modern Excel finally change the way Models are built v2.0.docx (eusprig.org)

 

If it is any use to you, I could also offer Lambda functions that read multidimensional data from an arbitrary cross-tab dataset (I have only gone to 4D but there is no reason why it should not be applied to higher dimensional data were that relevant).  The second Lambda function uses the first and generates a normalised dataset or will generate any of the normal pivot table crosstabs.  It is also possible to aggregate a column by addition.

 

Resources