actually, for this specific form of report, I would use =GROUPBY(Data[Item], Data[Value], ARRAYTOTEXT,,0) or =GROUPBY(Data[Item], Data[Value], LAMBDA(_,TEXTJOIN(" | ",,_)),,0) ...and of course, don't forget to sort the data
Great video! These are great functions that aren't covered enough! It's amazing how much MS is doing to Excel! I'm on the Beta Insider edition (Version 2409 Build 16.0.18011.20000), and when I was following along in the BYROW section around 5:17, when I typed the comma in the "=BYROW(J6#," functions popped up the way they do in the GROUPBY and PIVOTBY functions. I'm able to use the formula "=BYROW(J6#,SUM)*1.2" without having to use a LAMBDA! No such luck with the other functions, but something to look forward to!
This was introduced at the same time as GROUPBY/PIVOTBY. If a function has a single argument, you can use “Eta Reduction” Instead of LAMBDA(x,SUM(x)) you can just use SUM. It is syntax sugar. In the background Excel converts it to a LAMBDA for you and the applies the calculation. Only works with single argument functions, for multiple functions you have to use the full syntax.
Why "no luck"? =MAP(J6#,SUM)*1.2 =SCAN(,J6#,SUM)*1.2 =REDUCE(,J6#,SUM)*1.2 result as Mark's :) btw, in LAMBDA versions of these 3 functions, we do not need summing single cell since SUM(c)=c =SCAN(0, J6#, LAMBDA(a, v, a + v*1.2)) moreover, in Scenario #2, MAP is equivalent to =J6#*1.2 and REDUCE to =SUM(J6#)*1.2
Great video!! TJ can ignore empty so we can exclude filter: =MAP(F6#,LAMBDA(r,TEXTJOIN("|",,IF(Data[Item]=r,Data[Value],"")))) groupby alternative that looks cool: =GROUPBY(Data[Item],"|"&Data[Value]&"|",CONCAT,,0) Makes more sense and it's very easy to do scanning running totals "byrow" not cumulative. BYROW can not spill more than 1 val/row, REDUCE with VSTACK rows is the slowest method. We can still use SCAN with an accumulator that sets to 0 at the beginning of each row For an array "a" of x rows and y columns running tot for each row : =SCAN(0,SEQUENCE(x,y),LAMBDA(v,i,LET(m,MOD(i-1,y),IF(m,v,0)+INDEX(a,QUOTIENT(i-1,y)+1,m+1)))) for your data, clean running tot 1.2* byrow: =SCAN(0,SEQUENCE(3,4),LAMBDA(v,i,LET(m,MOD(i-1,4),IF(m,v,0)+1.2*INDEX(J6#,QUOTIENT(i-1,4)+1,m+1)))) returns: 74.4 154.8 224.4 328.8 80.4 158.4 234 319.2 96 210 272.4 370.8
Great video. Thank you! I rarely use these functions so that was really helpful. Btw this scenario is the reason why I'm head of heels with GROUPBY and PIVOTBY. First example: =SUBSTITUTE(GROUPBY(Data[Item],Data[Value],ARRAYTOTEXT,0,0),",","|") For the cross tabulated array, I'd use =SUMIFS(Data[Value],Data[Item],I6#) For PIVOTBY I'd first create my array in I5 with this function.=PIVOTBY(Data[Item],Data[Quarter],Data[Value],SUM,0,0,,1) And then =IFERROR(CHOOSECOLS(I5#,6)*1.2,"") Of course you can replace I5# with the PIVOTBY (PS I'm German and hopefully I didn't mess up the functions and the commas)
Simply great lesson! Thank you, Mark!😊 Recently I've started to learn these questions, but your video just get in time! Now I'll just apply it on my daily work! THANKS A LOT!🤗
Hi, you always post very informative videos. I have an Excel365 file which data from the odbc table which updates automatically in scheduled intervals, how can I sync that Excel data into Google Sheets with auto updates?
The different functions you highlight are interesting, but not something I think I can directly begin using. I am more interested in the 'Scenario #2' dynamic array row, columns, and single formula, since that's similar to how I need to summarize and present a large subset of my reporting. But is there a way to HSTACK or VSTACK a "Total" to both the horizontal and vertical arrays, and then have each row and column summed?
Here are the three formulas that I bashed out that gets the job done: rows with totals, columns with totals, and a final grand total. I'm not likely to ever use it as is, but it was an interesting exercise. Any thoughts though on if it could incorporate BYROW, MAP, SCAN, or REDUCE as used in the video? Column headers with sorted unique dates (text-formatted) and "TOTAL": =HSTACK(TRANSPOSE(TEXT(SORT(UNIQUE(tblData[DATE])),"mm/dd/yy")),"TOTAL") Row labels with unique customer numbers and "TOTAL" (with additional attributes for sorting & display): VSTACK(SORT(UNIQUE(tblData[[LOCNUM]:[CUSTNO]]),{1,2,3,4}),{"TOTAL","","",""}) And the data array, with totals (broken apart for easier reading): =IFERROR( LET( Dates,TRANSPOSE(SORT(UNIQUE(tblData[DATE]))), Custs,CHOOSECOLS(SORT(UNIQUE(tblData[[LOCNUM]:[CUSTNO]]),{1,2,3,4}),4), VSTACK( HSTACK( SUMIFS(tblData[AMOUNT],tblData[CUSTNO],Custs,tblData[DATE],Dates), SUMIFS(tblData[AMOUNT],tblData[CUSTNO],Custs)), SUMIFS(tblData[AMOUNT],tblData[DATE],Dates))), SUM(tblData[AMOUNT]))
Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0220 BYROW MAP SCAN REDUCE Comparison
These functions are part of Microsoft 365 subscriptions, if you're using a standalone version of Excel like Excel 2019 or earlier, these functions won't be available.
Explained briefly and concisely but very clearly and comprehensibly. If anyone has not understood it yet ...
Thank you. I appreciate that feedback. 😁
The most comprehensive tutorial on a very important new functions.
Thank you 😁
I think, this is the most comprehensive and clear comparative tutorial about these 4 LAMBDA helpers. Thanks!
Thank you. I appreciate that. 😁
This is the current best video about all those functions in excel.
Thank you, that is very kind of you to say. 😁
Perfectly summarized and structured, as always. Thanks a lot!
And a winner is... PIVOTBY :)
actually, for this specific form of report, I would use
=GROUPBY(Data[Item], Data[Value], ARRAYTOTEXT,,0)
or
=GROUPBY(Data[Item], Data[Value], LAMBDA(_,TEXTJOIN(" | ",,_)),,0)
...and of course, don't forget to sort the data
Yes, that would also be a solid choice for this scenario.
Great video! These are great functions that aren't covered enough!
It's amazing how much MS is doing to Excel! I'm on the Beta Insider edition (Version 2409 Build 16.0.18011.20000), and when I was following along in the BYROW section around 5:17, when I typed the comma in the "=BYROW(J6#," functions popped up the way they do in the GROUPBY and PIVOTBY functions. I'm able to use the formula "=BYROW(J6#,SUM)*1.2" without having to use a LAMBDA! No such luck with the other functions, but something to look forward to!
This was introduced at the same time as GROUPBY/PIVOTBY. If a function has a single argument, you can use “Eta Reduction”
Instead of LAMBDA(x,SUM(x)) you can just use SUM.
It is syntax sugar. In the background Excel converts it to a LAMBDA for you and the applies the calculation.
Only works with single argument functions, for multiple functions you have to use the full syntax.
Why "no luck"?
=MAP(J6#,SUM)*1.2
=SCAN(,J6#,SUM)*1.2
=REDUCE(,J6#,SUM)*1.2
result as Mark's :)
btw, in LAMBDA versions of these 3 functions, we do not need summing single cell since SUM(c)=c
=SCAN(0, J6#, LAMBDA(a, v, a + v*1.2))
moreover, in Scenario #2, MAP is equivalent to
=J6#*1.2
and REDUCE to
=SUM(J6#)*1.2
Thanks Mark, that’s looks clearer now, what each one does and the differences between them!
Great video!!
TJ can ignore empty so we can exclude filter:
=MAP(F6#,LAMBDA(r,TEXTJOIN("|",,IF(Data[Item]=r,Data[Value],""))))
groupby alternative that looks cool:
=GROUPBY(Data[Item],"|"&Data[Value]&"|",CONCAT,,0)
Makes more sense and it's very easy to do scanning running totals "byrow" not cumulative. BYROW can not spill more than 1 val/row, REDUCE with VSTACK rows is the slowest method.
We can still use SCAN with an accumulator that sets to 0 at the beginning of each row
For an array "a" of x rows and y columns running tot for each row :
=SCAN(0,SEQUENCE(x,y),LAMBDA(v,i,LET(m,MOD(i-1,y),IF(m,v,0)+INDEX(a,QUOTIENT(i-1,y)+1,m+1))))
for your data, clean running tot 1.2* byrow:
=SCAN(0,SEQUENCE(3,4),LAMBDA(v,i,LET(m,MOD(i-1,4),IF(m,v,0)+1.2*INDEX(J6#,QUOTIENT(i-1,4)+1,m+1))))
returns:
74.4 154.8 224.4 328.8
80.4 158.4 234 319.2
96 210 272.4 370.8
That is next level - great work. ✅
Great video. Thank you! I rarely use these functions so that was really helpful.
Btw this scenario is the reason why I'm head of heels with GROUPBY and PIVOTBY.
First example: =SUBSTITUTE(GROUPBY(Data[Item],Data[Value],ARRAYTOTEXT,0,0),",","|")
For the cross tabulated array, I'd use =SUMIFS(Data[Value],Data[Item],I6#)
For PIVOTBY I'd first create my array in I5 with this function.=PIVOTBY(Data[Item],Data[Quarter],Data[Value],SUM,0,0,,1)
And then =IFERROR(CHOOSECOLS(I5#,6)*1.2,"") Of course you can replace I5# with the PIVOTBY
(PS I'm German and hopefully I didn't mess up the functions and the commas)
Nice options. Good work. 👍
Very nice Mark, thank you. I would like to see more videos on SCAN and REDUCE
You are right, there aren’t many resources about those functions around.
Simply great lesson! Thank you, Mark!😊
Recently I've started to learn these questions, but your video just get in time!
Now I'll just apply it on my daily work!
THANKS A LOT!🤗
Thanks. 😁
Great tactic, instantly applying in the real world, helps to embed the knowledge.
@@ExcelOffTheGrid unfortunatelly, my filters and xlookups return me CALC error when I applying byrow function with lambda like in video.🤔
I've found the answer: ARRAYTOTEXT or another text join function.😊 Thanks!
“Let’s go!”? What happened to “Let’s get started!”? New functions, new catchphrases - same great explanations from Mark.
Ha ha ha - a little change to keep your on your toes 🤣
Another great video, love how your exploring Lambda more
I’m glad you’re enjoying it. I’m sure there will be more to come!
This is great, thanks! 👏
Thank you. Glad you like it!
Mind blown... Love it ! 🤯🤯 Thanks for sharing
Glad you liked it! 😁
Very cool thanks Mark!
Thank you. I’m glad you like it.
Super quality of information🎉🎉❤
Thank you so much 😁
Great content
Made my head hurt, but at least I understood it lol
LAMBDAs are a tricky concept. But keep working at it; it’s worth it.
Hi, you always post very informative videos. I have an Excel365 file which data from the odbc table which updates automatically in scheduled intervals, how can I sync that Excel data into Google Sheets with auto updates?
Sorry - Google Sheets isn’t my bag. Can’t help you there.
The different functions you highlight are interesting, but not something I think I can directly begin using. I am more interested in the 'Scenario #2' dynamic array row, columns, and single formula, since that's similar to how I need to summarize and present a large subset of my reporting. But is there a way to HSTACK or VSTACK a "Total" to both the horizontal and vertical arrays, and then have each row and column summed?
Here are the three formulas that I bashed out that gets the job done: rows with totals, columns with totals, and a final grand total. I'm not likely to ever use it as is, but it was an interesting exercise. Any thoughts though on if it could incorporate BYROW, MAP, SCAN, or REDUCE as used in the video?
Column headers with sorted unique dates (text-formatted) and "TOTAL":
=HSTACK(TRANSPOSE(TEXT(SORT(UNIQUE(tblData[DATE])),"mm/dd/yy")),"TOTAL")
Row labels with unique customer numbers and "TOTAL" (with additional attributes for sorting & display):
VSTACK(SORT(UNIQUE(tblData[[LOCNUM]:[CUSTNO]]),{1,2,3,4}),{"TOTAL","","",""})
And the data array, with totals (broken apart for easier reading):
=IFERROR(
LET(
Dates,TRANSPOSE(SORT(UNIQUE(tblData[DATE]))),
Custs,CHOOSECOLS(SORT(UNIQUE(tblData[[LOCNUM]:[CUSTNO]]),{1,2,3,4}),4),
VSTACK(
HSTACK(
SUMIFS(tblData[AMOUNT],tblData[CUSTNO],Custs,tblData[DATE],Dates),
SUMIFS(tblData[AMOUNT],tblData[CUSTNO],Custs)),
SUMIFS(tblData[AMOUNT],tblData[DATE],Dates))),
SUM(tblData[AMOUNT]))
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-signup/
File Reference for this video: 0220 BYROW MAP SCAN REDUCE Comparison
These functions are part of Microsoft 365 subscriptions, if you're using a standalone version of Excel like Excel 2019 or earlier, these functions won't be available.
Yes, very true.