User Profile
boukasa
Brass Contributor
Joined 4 years ago
User Widgets
Recent Discussions
Running total with reset, lambda
I use RunTotCols below to do a running total on a column: EatCols = LAMBDA(arr, LET(rows, ROWS(arr), res, N(SEQUENCE(, rows) >= SEQUENCE(rows)), res)); // Converts an array's columns into a running total RunTotCols = LAMBDA(arr, LET(res, TRANSPOSE(MMULT(TRANSPOSE(arr), EatCols(arr))), res)); And I use Reset below to determine when a column's value changes: PushRows = LAMBDA(addarr, target, LET( newrowct, ROWS(addarr), oldrowct, ROWS(target), choparr, CHOOSEROWS(target, SEQUENCE(oldrowct - newrowct)), r, VSTACK(addarr, choparr), r ) ); Reset =LAMBDA(col, LET( prevcol, PushRows({""}, col), same, (col = prevcol) + (SEQUENCE(ROWS(col))=1), same )); I need to combine these ideas so that I get a running total that resets when a corresponding label column changes, but how to do this escapes me. Any suggestions? Thanks so much.520Views0likes1CommentAdvanced Formula Environment Lambda definition appears to be cached/unreliable [Excel, Lambda, AFE]
I have run into this situation several times now: I use a Lambda defined/managed with AFE, and after the Lambda is edited, the pre-edited version of the lambda is used when it is referenced by another Lambda. It is as if the called Lambda has been "cached" inside the calling Lambda. The only way I have found to "fix" the issue is to rename the "cached" lambda and change its usage throughout AFE and the workbook. This both very time consuming, as you spend hours tracking down what appears to be a bug only to find out it is actually just a wrong result being produced by Excel/AFE - and it is also very dangerous, as the tested Lambda is not guaranteed to be what is actually being run. Here is an example from a complex workbook: There is a Lambda called Later. I call Later from my sheet and the result is 0, which is correct. I call Later from another Lambda called LaterTest and the result is -12836.69, which is wrong, and was the value that the Later lambda returned a few weeks ago before it was subsequently edited. LaterTest is a Lambda I *just made* so the "caching" is somewhere behind that. I duplicate the Later lambda word-for-word into a new lambda called Later2. I call Later2 from my sheet and the result is 0, which is correct. I call Later2 from another Lambda called Later2Test and the result is 0, as it should be. To state the root issue very clearly: two identical lambdas produce the *same* results when called from the sheet, and *different* results when called from another Lambda. The different result is an old version of the first lambda which has subsequently been edited. Any workaround would be greatly appreciated - it's currently impossible to know which lambdas in a codebase are going to produce incorrect results, and unfortunately I now have thousands of lines of code in AFE. Results: =Reconcile.Later() 0 =Reconcile.LaterTest() -12836.69 =Reconcile.Later2() 0 =Reconcile.Later2Test() 0 AFE Code: // Total amount of reconciliation amounts in the reconcile table that are // later than the current statement. Later = LAMBDA( LET( ThisAccount, Reconciliations[Account] = Account(), LaterRecs, (Reconciliations[Statement Date] > StatementDate()), ThisLLC, Reconciliations[LLC] = LLC(), Include,ThisAccount * ThisLLC * LaterRecs * (Reconciliations[Completed] = "Y"), End, SUM(FILTER(Reconciliations[End Balance], Include, 0)), Start, SUM(FILTER(Reconciliations[Start Balance], Include, 0)), End - Start ) ); // Total amount of reconciliation amounts in the reconcile table that are // later than the current statement. Later2 = LAMBDA( LET( ThisAccount, Reconciliations[Account] = Account(), LaterRecs, (Reconciliations[Statement Date] > StatementDate()), ThisLLC, Reconciliations[LLC] = LLC(), Include,ThisAccount * ThisLLC * LaterRecs * (Reconciliations[Completed] = "Y"), End, SUM(FILTER(Reconciliations[End Balance], Include, 0)), Start, SUM(FILTER(Reconciliations[Start Balance], Include, 0)), End - Start ) ); LaterTest = LAMBDA(Later()); Later2Test = LAMBDA(Later2());685Views0likes1CommentIncrement a column only when a corresponding column changes, in a Lambda
I am trying to write a lambda that takes a sorted array an produces a corresponding numbering column that increments when the value in the source array changes. Like this: param result 1 1 3 2 3 2 7 3 10 4 11 5 11 5 14 6 I have a number of problems like this that are easy to do in a sheet with a formula, but I need to accomplish them in a Lambda, preferably using native array formulas/sequences rather than iterating. How do I solve this particular problem, and is there are general way to think about this class of problems?Solved2.2KViews0likes7CommentsConditional Formatting via Lambda
I have a Lambda that produces an array of true/false values. I want to conditionally format a data range where true values produced by the Lambda are in bold. Like this: Data Range: A B C D E F G H I Lambda result: T F F F T F T F F Desired outcome: A, E, and G are bold If I materialize the results of the Lambda in a range, I can use the materialized range as the condition, using the upper left corner of the materialized condition range as a relative reference as applied to the data range. But if I use the Lambda itself in the condition (=GetFormatArray()) the conditional formatting does not work - each data range cell is evaluated against the top left result of the formula, so they all get the same condition result and are formatted the same way as dictated by the upper left cell of the array produced by the lambda. How can I spread the lambda result across the data range as conditional formatting? Any technique to do this other than hidden sheets that materialize the lambda? (I wish Excel would remove/hide the distinction between a range and an array so they were seamless and interchangeable! You almost don't need the sheet anymore at that point, and you could output the language result any way you want including via API. That would be amazing!)2.2KViews0likes2CommentsMMULT to pivot data totals rather than SUMIFS
In this post, SergeiBaklanexplained how to use MMULT rather than SUMIF when the input data is an array rather than a range. How does this extend to pivoting summed data like SUMIFS does when the input is an array? Example - say I have this data at A1: item color sold a red 3 b blue 6 a blue 2 c orange 1 a red 4 I can pivot the summed data like this: =LET( sold,C2:C6, item,A2:A6, color, B2:B6, rows,UNIQUE(item), cols,UNIQUE(TRANSPOSE(B2:B6),1), SUMIFS(sold,item,rows,color,cols) ) 7 2 0 0 6 0 0 0 1 But if the columns are arrays, SUMIFS won't do: =LET( sold,{3;6;2;1;4}, item,{"a";"b";"a";"c";"a"}, color, {"red";"blue";"blue";"orange";"red"}, rows,UNIQUE(item), cols,UNIQUE(TRANSPOSE(color),1), r,SUMIFS(sold,item,rows,color,cols), r ) #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! I see how to use MMULT to sum the rows and the columns, and how to count the number of matching items: =LET( sold,{3;6;2;1;4}, item,{"a";"b";"a";"c";"a"}, color, {"red";"blue";"blue";"orange";"red"}, rows,UNIQUE(item), cols,UNIQUE(TRANSPOSE(color),1), prodmatch,--(rows=TRANSPOSE(item)), prodsum,MMULT(prodmatch, sold), colormatch,--(cols=color), colorsum, MMULT(TRANSPOSE(sold),colormatch), countmatch,MMULT(prodmatch,colormatch), countmatch ) 2 1 0 0 1 0 0 0 1 But how do I sum the matching items? Thanks for any help.1.6KViews0likes3CommentsAre realized spills a different type of thing than the same result calculated in LET?
When I use the # construct to utilize realized spilled output as input into SUMIF it works, but if I use the same data calculated in LET it does not. I assume this means the realized spill is one type of data and the result of the formula that generated the spilled data is a different type of data. Is that true? What is the difference? Here is an example.Solved1.5KViews0likes4CommentsFilter an array row-by-row, or sort row-by-row
How can an array be filtered row by row? I have an array that has names and FALSE as entries. I'm trying to remove the FALSE entries. FILTER does not appear to let me work each row separately, and LAMBDA does not allow output of an array, only a scalar. I'm looking for an array solution, not a copy-down. Is this possible? I've attached an example. (I truly am mystified by the FILTER function's behavior with array parameters. A single row works fine - FILTER(onerowarray,onerowarray<>FALSE) - but multiple rows produce #VALUE.)3KViews0likes9Commentsfilter using criteria from list/array
The basic question is, how can I utilize FILTER next to an array to produce filtered results for a list of items? FILTER does not appear to allow me to use an array reference in the criterion. Is there a workaround? (UPDATE: I mean, is there a workaround that produces maintenance-free spill-type results.) Example - imagine I have an array like so: Projects A B C In a separate part of the workbook I have a database of projects and eligible volunteers like so: Volunteers Project Code Eligible Volunteers A Mary A John B John B Sue C Peg C Mary C John I want to use the project code as the criterion for a filter, and show the filter results next to the code, like so: Project Code Volunteers A Mary John B John Sue C Peg Mary John I expected to use something like this: =TRANSPOSE(INDEX(FILTER(EligibleVolunteers, EligibleVolunteers[Project Code]=Projects),,2)) But when I use array Projects in the criteria field, I get #n/a I can rewrite the formula to use a cell reference to the Project Code, and drag it down, and it works. But that makes the spreadsheet not automatic anymore. Is there a workaround or different approach? Also, what's the rationale for not allowing a dynamic reference in the criteria parameter?Solved4.8KViews0likes12CommentsOFFSET on results of FILTER
I assume I'm making a basic error or overlooking something silly. _test is a 5 column table with a column Bool. I want the first two columns only, for those rows where Bool is "Yes". I have a function like this: =LET( matches,FILTER(_test,_test[Bool]="Yes","None"), shows, OFFSET(matches,0,0,,2), shows ) I get a #value error. If I display matches instead of shows, I see my 2 rows. If I break it up into two separate calculations, I get the correct result: =LET( matches,FILTER(_test,_test[Bool]="Yes","None"), matches ) gives me two rows, and then (result being in C10) =LET( shows,OFFSET(C10#,0,0,,2), shows) gives me the two column result I wanted. Why doesn't OFFSET(FILTER()) work, but OFFSET(materialized filter data) does?Solved12KViews0likes1CommentProduct of numbers in a row in a dynamic array
How does one do row-by-row evaluations with a 2d dynamic array? For example, I need the product of all the numbers in each row in an array. I can only seem to find ways to get the product of all the numbers in all rows of the array, reduced to a single number. My spilled array: 0 2 2 2 1 1 3 3 2 Result sought: 0 2 18 from 0*2*2 2*1*1 3*3*2 I've struggled mightily and I'm about to resort to some very ugly text manipulation plus a name with evaluate based on the fact that I know my numbers will all be single digits 😞 I really do not want to do this! =LET( rows, ROW(array)-ROW(INDEX(array,1,1)), width, COLUMNS(array), astext, TEXTJOIN("*",,array)&"*", MID(astext,rows*width*2+1,width*2-1) ) It seems absurd that I can make four rows of text, but not fours rows of results. Thanks for any insights.Solved9.4KViews0likes21Comments
Groups
Recent Blog Articles
No content to show