Auto commentary function

[Aussiebear]

I recently came across this Lambda function in a worksheet.



=AUTOCOMMENTARY(Heading, ComparativeName, SubHeadings, BaseValues, ComparativeValue, Reasons, _
SortOrder, PlusWord, MinusWord, StatementNumberFormat, VarianceNumberFormat, Threshold, DecimalAccuracy)

In the attached workbook there are a number of examples of how it may be applied. My question is largely based on where does this formula reside? It doesn't appear to be an Office 365 for Mac function.

[georgiboy]

It's a custom function that has been created from what I can see, you will find it in the name manager. It has been added to the spreadsheet itself, it kind of acts like a UDF in the way it works, difference being, it is a LAMBDA function rather than a UDF.

Below is some of the logic:

=LAMBDA(Heading,ComparativeName,SubHeadings,BaseValues,ComparativeValues,Re asons,SortOrder,PlusWord,MinusWord,StatementNumberFormat,VarianceNumberForm at,Threshold,DecimalAccuracy,LET(BaseTotal, ROUND(SUM(BaseValues), DecimalAccuracy), ComparativeTotal, ROUND(SUM(ComparativeValues), DecimalAccuracy), VarianceTotal, ROUND(BaseTotal - ComparativeTotal, DecimalAccuracy), VarianceValues, ROUND(BaseValues - ComparativeValues, DecimalAccuracy), SortOrderValues, IF(ABS(SortOrder) = 2, ROUND(ABS(BaseValues - ComparativeValues), DecimalAccuracy), ROUND(BaseValues - ComparativeValues, DecimalAccuracy)), isAre, IF(RIGHT(Heading, 1) = "s", "are", "is"), TempArray, SORT(FILTER(HSTACK(VarianceValues, SortOrderValues, SubHeadings & " variance of " & TEXT((VarianceValues), VarianceNumberFormat) & IF(ISBLANK(Reasons), "", " due to " & Reasons)), ABS(VarianceValues) >= Threshold, {0,0,"N/A"}), 2, IF(SortOrder < 0, -1, 1)), ReasonList, TEXTJOIN("; ", TRUE, DROP(TempArray, , 2)), UnanalyzedTotal, VarianceTotal - ROUND(SUM(TAKE(TempArray, , 1)), DecimalAccuracy), ReasonsError, ISERROR(TAKE(TempArray, 1, 1)), IndexItem, SWITCH(TRUE, VarianceTotal = 0, 1, ABS(VarianceTotal) < Threshold, 1, ReasonsError = TRUE, 2, 3), TempStack, VSTACK(Heading & " of " & TEXT(BaseTotal, StatementNumberFormat) & " " & isAre & " " & "inline with " & ComparativeName & ".", Heading & " of " & TEXT(BaseTotal, StatementNumberFormat) & " " & isAre & " " & TEXT(VarianceTotal, VarianceNumberFormat) & " " & IF(VarianceTotal < 0, MinusWord, PlusWord) & " " & ComparativeName & ". This is driven by variances below " & TEXT(Threshold, StatementNumberFormat) & ".", Heading & " of " & TEXT(BaseTotal, StatementNumberFormat) & " " & isAre & " " & TEXT(VarianceTotal, VarianceNumberFormat) & " " & IF(VarianceTotal < 0, MinusWord, PlusWord) & " " & ComparativeName & "." & " This is driven by " & ReasonList & IF(UnanalyzedTotal = 0, ".", "; the remaning varaince of " & TEXT(UnanalyzedTotal, VarianceNumberFormat) & " relates to items below " & TEXT(Threshold, StatementNumberFormat) & ".")), CHOOSEROWS(TempStack,

The rest seems to cut off for me when i try to copy it.

[Aussiebear]

There's something about these Lambda functions..... they tend to hide the War & Peace novels all too neatly. Yoda. I need your help please.

[georgiboy]

I tend not to use LAMBDA in this way, I tend to use it within a formula that requires LAMBDA. Thats not to say there is anything wrong with using it in this way. As you stated, it hides the logic.

"Long this formula is"

[Aussiebear]

I confused am.