Mike, this to me is one of the best explanations to date of this topic. Now, there is even more to this challenge than you mentioned in the video. Although it is true that you run into an array-within-array issue as the first and innermost problem, and that stops you cold in your tracks, but there’s another problem, too: SUM, which is an aggregator. So the result would never be a vector as a result of that. For me the most striking lesson is this: BYCOL and the like allow us to vectorize any formula, regardless of the fact whether they use aggregators, or not. Too bad I don’t use those functions yet because I can’t share them with my colleagues or solve their problems using those functions because they don’t have access to them. A shame, MS should roll them out to everyone.
Thanks for the kind words, Geert : ) : ) Yes, the story side of this one: building up the formula until H10:H133 did not work, and then, introducing BYROW and LAMBDA as the rescue package to iterate row-by-row is a new story, a better story than ones I told in the past. At least I hope. And yah, BYCOL, BYROW and MAP help to "vectorized" our formula!!!
Yes and there are many more issues besides aggregators vs iterators such as changing Filter Context etc etc. Hopefully MS has more such LAMBDA helpers in the oven. If the new tools are designed properly the vectorization of Excel will grow geometrically relatve to the number of new tools. It may be a Yogi-ism but its true for Excel: the future is yet to come!! : ) : ) : )
@@richardhay645 I don't like the Yankees, but I LOVE Yogi : ) : ) : ) It gets even better: given our current set of awesome Excel tools, the future is yet to come for our imaginative use in creating solutions!!!!!!!!
@@excelisfun the number one way for current excel students to prepare for that vectorized future is to develop at least an intermediate level grasp of Linear Algebra. Such knowledge already makes understanding DA/LAMBDA Helper functions MUCH easier (equally true for DAX). But in the future Linear Algebra will be the essential key to even accessing these tools. This is the directiim most of business analysis is moving with finance and statistics leading the way.
Thank you Mike!! ✌For fun , a formula that uses MAP with 3 arrays: sr=IF(SEQUENCE(,4),B10:B13) ; pr=IF(SEQUENCE(4),C9:F9) ; cm=C10:F13 total commissions 2D array: tc=MAP(sr,cm,pr,LAMBDA(x,y,z,SUM((x=B16:B71)*(z=C16:C71)*D16:D71*y))) - for tot comm. (sales rep/all prod) => =BYROW(tc,LAMBDA(x,SUM(x))) - for tot comm. (prod/all sales rep) => =BYCOL(tc,LAMBDA(x,SUM(x)))
Awesome Mike! Another excellent lesson :)) Worth noting that in this case BYROW and MAP are interchangeable. If you write the same formula, but use MAP instead, you get the same set of spilled results. Thanks for the LAMBDA fun! Looking forward to the final video. Thumbs up!!
@@excelisfun a colleague had a data set of 3 columns A B C he wanred a list of unique value in A and B, and concatenate the values found in C. Could be done with M code, or Dax… but require the refresch steps.. I thought that the dynimic spill array would be perfect for this problematic. I just missed the BYROW / LAMDA to concatenate values from column C by row contaext. Your video helped finalizing my spilled array formula ;) The final need was completely different, but it was a common sense in the approach. My output formula was. =HSTACK(LET( a; UNIQUE(FILTER(A:B;A:A""));a); BYROW(LET( a; UNIQUE(FILTER(A:B;A:A""));a); LAMBDA(r; LET( coo;FILTER(A:C;A:A=INDEX(r;;1);B:B=INDEX(r;;2)); row_coo;ROWS(coo); list_coo;INDEX(coo;SEQUENCE(row_coo);3); TEXTJOIN(";";1;list_coo) )))). It is like doing a doing a CONCATENATEX in DAX , but in excel worsheet formula :)
@@Anthony_Lecoq Very cool. So elaborate and yes, it is like CONCATENATEX in DAX. I do have some advice, avoid full column references at all costs. Although this is a common practice, and although the Excel Calc Engine can deal with the better than in the past, they can cause big problems when rouge data enters spreadsheet and may can calculations time to increase.
@@excelisfun thanks Mike for the good practices. I just tried it on a very small data set with Excel 365 on Ipad :) I should definitely try to challenge this kind of dynamic spill array with a several hundreds k lines from a computer next week ;) In DAX we have DAX studio to analyze the performance of measure. Do you know an equivalent to evaluate the performance of the Excel formula calculation engine ? (Except looking at the watch and wait 😌 ) ?
In reference to my comment on your earlier version (before the benching of poor MAP) of this video, the use of a LAMBDA helper that I was referencing: one that without complicating the solution would expand the domain of worksheet Excel rather than finding a more complex way of doing the same task Of course one way of looking at this exsmple is that the methods of video 2 several days ago are a bit simpler and that BYROWS facilitated a spill and the elimination of a physical helper column which in some situations might be reasonable goals. But I think the real contibution of the LAMDA helper in thi case is to solve, at least in some situations, the array within an array conundrum. This type of use of LAMBDA helper functions IS the futer of Excel.
I agree: LAMBDA is really more complicated than the helper column, but when you need to spill, the extra complication is required. I also agree that BYROW, BYCOLUM, MAP with LAMBDA really to help with the array within array problem. Bam: Richard two for two right on!!!!
@@excelisfunYeah, the array within an array" limitation has prevented the solution of many relevant problems simce the DOS age. But without other associative benefits, just the thrill of the spill rationale for more complicated solutions has almost zero magnetic attraction for me!!
@@richardhay645 I agree, mostly. Formulas like these are almost exclusively relegated to single cell report formulas. That is next video : ) But, as I get better and better with them, the simplicity of not have to lock references and not copying the formula and editing in a single cell keeps growing on me ; )
@@excelisfun hear, hear! Respectfully, for me vectorisation is “everything”. For all the reasons you mentioned, and also, in our work calculations like these never stand alone, but are part of a larger chain of calculations. Being able to do things in a vectorised way helps a lot in such a context. Not to mention that ‘tables’ can dynamically grow in width depending on a parameter. I seek to vectorise as much as possible.
Thank you so much for this detailed explanation for each function! I was wondering about this situation… I have a range of numbers that I need to sum so I highlight the whole range and then use BYROW to sum up the ranges for each row. That works like expected. However, I wanted to add an if statement that checks whether the first column is blank or not. If it is, the cell should be blank. If not, it should continue with the summing. You can’t put multiple arrays into BYROW so I didn’t know how to approach this situation. I tried wrapping the BYROW with MAP but that gives me errors. Then I tried doing an ArrayFormula and I got errors. Would you have any suggestions for how I might do this?
One can always layer on more functionality. Wherever one has Lambda, one always has the option of naming it to provide meaning. For example, my formula reads = MAP(SR, Commissionλ(salesRep, salesValue, listRep, commissionRate)) The Lambda function takes additional parameters to avoid hard-wiring specific ranges into the calculation. Commissionλ = LAMBDA(sRep, sVal, lRep, rate, LAMBDA(rep, LET( repSales, FILTER(sVal, sRep=rep), repRates, XLOOKUP(rep, lRep, rate), SUM(repRates*repSales) ) )); The 'double-banked' Lambdas allow me to pass the lookup tables whilst the MAP helper function runs through the list of sales representatives. By the way, I did not perform a two-way lookup since both the sales table and the commission table have the same headers and the rates can be applied to the product array without further lookups.
Thanks for the formula, Peter B!!! I tried to implement your formula. This is what I tried: = LAMBDA(sRep,sVal,lRep,rate, LAMBDA(rep, LET( repSales, FILTER(sVal, sRep=rep), repRates, XLOOKUP(rep, lRep, rate), SUM(repRates*repSales) ) ))(B18:B31,C18:F31,B10:B13,C10:F13,L57:L60) But I did not get it to work. Any ideas?
@@excelisfun Yes. I have used two Lambda functions, so two sets of parameters are needed. These are not nested so they are read left to right. = LAMBDA(sRep,sVal,lRep,rate, LAMBDA(rep, LET( repSales, FILTER(sVal, sRep=rep), repRates, XLOOKUP(rep, lRep, rate), SUM(repRates*repSales) ) ))(B18:B31,C18:F31,B10:B13,C10:F13)(L57) The function is only capable of performing the calculation for a single sales rep, hence my second parameter is a single cell and not a range. To get the result as a dynamic array, the entire function is placed within MAP. MAP takes each sales rep in turn and provides the name in place of the existing L57 reference.
Hi, love your channel, I appreciate your effort and hope you enjoy keeping your amazing channel! But how about improving your sound a bit? maybe a new microphone that does justice to your pleasent voice? heh heh... Anyway, keep on doing this~, and God bless you!
@@excelisfun Thank you so much for your reply. I am starstrucked. I apologize for making you confused. I had thought about this when I was watching your older videos. You have definitely superb quality audio and it has been very long also. It was my phone's speaker that needed trashing. (Your sound is excellent through my car audio) Don't need to change a thing! Hope you didn't waste your time watching youtube on how to improve your sound because you don''t need it.
You Tube has had really bad comment errors for a few years. All of use, you, me and many other Teammates have had comments mysteriously deleted. And RUclips has not fixed it... : (
Thanks Mike, the power of these new features is incredible! A warm hug for you from a distance.
Thanks for the distance hug : ) : ) : )
😄Wham! The External Auditors are going crazy...! Great Tutorial Mike. Thanks for sharing!
The auditors better get caught up with the latest in Excel if they want to keep there audit job lol : ) : )
Thanks Mike and ExcelLambda.
Go Team!!!!!!!!
Thanks Mike for the EXCELlent video.
You are welcome, Fellow Teacher : ) : ) : )
Boom!This Super Neat Formula Put A Big Smile On My Face,Simply Awesome...Thank You Mike :)
Yes, smiles are the best!!!! We have such a great Team!!!!!
i love u issacs dad thank you for teaching me how to use excel
You are welcome!!! Thanks for the love, Tyre_!!!! : ) : ) : )
Mike, this to me is one of the best explanations to date of this topic.
Now, there is even more to this challenge than you mentioned in the video.
Although it is true that you run into an array-within-array issue as the first and innermost problem, and that stops you cold in your tracks, but there’s another problem, too: SUM, which is an aggregator. So the result would never be a vector as a result of that.
For me the most striking lesson is this: BYCOL and the like allow us to vectorize any formula, regardless of the fact whether they use aggregators, or not. Too bad I don’t use those functions yet because I can’t share them with my colleagues or solve their problems using those functions because they don’t have access to them. A shame, MS should roll them out to everyone.
Thanks for the kind words, Geert : ) : ) Yes, the story side of this one: building up the formula until H10:H133 did not work, and then, introducing BYROW and LAMBDA as the rescue package to iterate row-by-row is a new story, a better story than ones I told in the past. At least I hope. And yah, BYCOL, BYROW and MAP help to "vectorized" our formula!!!
I have the same problem here at my work. Can't share any of the fun yet... : (
Yes and there are many more issues besides aggregators vs iterators such as changing Filter Context etc etc. Hopefully MS has more such LAMBDA helpers in the oven. If the new tools are designed properly the vectorization of Excel will grow geometrically relatve to the number of new tools. It may be a Yogi-ism but its true for Excel: the future is yet to come!! : ) : ) : )
@@richardhay645 I don't like the Yankees, but I LOVE Yogi : ) : ) : ) It gets even better: given our current set of awesome Excel tools, the future is yet to come for our imaginative use in creating solutions!!!!!!!!
@@excelisfun the number one way for current excel students to prepare for that vectorized future is to develop at least an intermediate level grasp of Linear Algebra. Such knowledge already makes understanding DA/LAMBDA Helper functions MUCH easier (equally true for DAX). But in the future Linear Algebra will be the essential key to even accessing these tools. This is the directiim most of business analysis is moving with finance and statistics leading the way.
Thank you Mike!! ✌For fun , a formula that uses MAP with 3 arrays: sr=IF(SEQUENCE(,4),B10:B13) ; pr=IF(SEQUENCE(4),C9:F9) ; cm=C10:F13
total commissions 2D array:
tc=MAP(sr,cm,pr,LAMBDA(x,y,z,SUM((x=B16:B71)*(z=C16:C71)*D16:D71*y)))
- for tot comm. (sales rep/all prod) => =BYROW(tc,LAMBDA(x,SUM(x)))
- for tot comm. (prod/all sales rep) => =BYCOL(tc,LAMBDA(x,SUM(x)))
That is very wild, Excel Lambda!!! You upper level Mastery is amazing : ) : ) : ) : )
Thanks for the formula for this video, Excel Lambda : ) : ) : ) : ) : )
@@excelisfun Anytime!! Excel Is Fun !!✌😉🙏
@@Excelambda : ) : )
"We cann't do an array calculation within an array calculation" ... wow ...you already answered my question without even i ask ... thanks Mike
: ) : )
Excel is coming much more flexible with LAMDA Thanks Mike 🙂
You are flexibly welcome, Mohamed!!!!
How amazing this is!!! 😍😍😍😍
Glad it is amazing for you, Dimitris!!!
Awesome Mike! Another excellent lesson :)) Worth noting that in this case BYROW and MAP are interchangeable. If you write the same formula, but use MAP instead, you get the same set of spilled results. Thanks for the LAMBDA fun! Looking forward to the final video. Thumbs up!!
Good point, Wayne!!!! I tend to use BYROW and BYCOL when I can because it is easier to understand for my slow brain lol
@@excelisfun Likewise.. BYROW and BYCOL are easier to visualize. Go Team!!!
@@wayneedmondson1065 Go Team!!!!
Thank you Mike, Great video. You are like an angel, your video are always coming to solve an issue I am struggling with 😅
You were just struggling with this? In what way?
I am always happen to be part of a great Team : )
@@excelisfun a colleague had a data set of 3 columns A B C he wanred a list of unique value in A and B, and concatenate the values found in C. Could be done with M code, or Dax… but require the refresch steps.. I thought that the dynimic spill array would be perfect for this problematic. I just missed the BYROW / LAMDA to concatenate values from column C by row contaext. Your video helped finalizing my spilled array formula ;) The final need was completely different, but it was a common sense in the approach. My output formula was. =HSTACK(LET( a; UNIQUE(FILTER(A:B;A:A""));a); BYROW(LET( a; UNIQUE(FILTER(A:B;A:A""));a); LAMBDA(r; LET(
coo;FILTER(A:C;A:A=INDEX(r;;1);B:B=INDEX(r;;2));
row_coo;ROWS(coo);
list_coo;INDEX(coo;SEQUENCE(row_coo);3);
TEXTJOIN(";";1;list_coo)
)))). It is like doing a doing a CONCATENATEX in DAX , but in excel worsheet formula :)
@@Anthony_Lecoq Very cool. So elaborate and yes, it is like CONCATENATEX in DAX. I do have some advice, avoid full column references at all costs. Although this is a common practice, and although the Excel Calc Engine can deal with the better than in the past, they can cause big problems when rouge data enters spreadsheet and may can calculations time to increase.
@@excelisfun thanks Mike for the good practices. I just tried it on a very small data set with Excel 365 on Ipad :) I should definitely try to challenge this kind of dynamic spill array with a several hundreds k lines from a computer next week ;) In DAX we have DAX studio to analyze the performance of measure. Do you know an equivalent to evaluate the performance of the Excel formula calculation engine ? (Except looking at the watch and wait 😌 ) ?
@@Anthony_Lecoq Most of us use Excel MVP Charles Williams add-in called Fast Excel.
In reference to my comment on your earlier version (before the benching of poor MAP) of this video, the use of a LAMBDA helper that I was referencing: one that without complicating the solution would expand the domain of worksheet Excel rather than finding a more complex way of doing the same task
Of course one way of looking at this exsmple is that the methods of video 2 several days ago are a bit simpler and that BYROWS facilitated a spill and the elimination of a physical helper column which in some situations might be reasonable goals. But I think the real contibution of the LAMDA helper in thi case is to solve, at least in some situations, the array within an array conundrum. This type of use of LAMBDA helper functions IS the futer of Excel.
I agree: LAMBDA is really more complicated than the helper column, but when you need to spill, the extra complication is required. I also agree that BYROW, BYCOLUM, MAP with LAMBDA really to help with the array within array problem. Bam: Richard two for two right on!!!!
@@excelisfunYeah, the array within an array" limitation has prevented the solution of many relevant problems simce the DOS age. But without other associative benefits, just the thrill of the spill rationale for more complicated solutions has almost zero magnetic attraction for me!!
@@richardhay645 I agree, mostly. Formulas like these are almost exclusively relegated to single cell report formulas. That is next video : ) But, as I get better and better with them, the simplicity of not have to lock references and not copying the formula and editing in a single cell keeps growing on me ; )
@@excelisfun hear, hear! Respectfully, for me vectorisation is “everything”. For all the reasons you mentioned, and also, in our work calculations like these never stand alone, but are part of a larger chain of calculations. Being able to do things in a vectorised way helps a lot in such a context. Not to mention that ‘tables’ can dynamically grow in width depending on a parameter. I seek to vectorise as much as possible.
@@GeertDelmulle Thanks, Geert "Vectorize Everything" Delmulle : ) : )
Thank you so much for this detailed explanation for each function! I was wondering about this situation… I have a range of numbers that I need to sum so I highlight the whole range and then use BYROW to sum up the ranges for each row. That works like expected. However, I wanted to add an if statement that checks whether the first column is blank or not. If it is, the cell should be blank. If not, it should continue with the summing. You can’t put multiple arrays into BYROW so I didn’t know how to approach this situation. I tried wrapping the BYROW with MAP but that gives me errors. Then I tried doing an ArrayFormula and I got errors. Would you have any suggestions for how I might do this?
One can always layer on more functionality. Wherever one has Lambda, one always has the option of naming it to provide meaning. For example, my formula reads
= MAP(SR, Commissionλ(salesRep, salesValue, listRep, commissionRate))
The Lambda function takes additional parameters to avoid hard-wiring specific ranges into the calculation.
Commissionλ
= LAMBDA(sRep, sVal, lRep, rate, LAMBDA(rep,
LET(
repSales, FILTER(sVal, sRep=rep),
repRates, XLOOKUP(rep, lRep, rate),
SUM(repRates*repSales)
)
));
The 'double-banked' Lambdas allow me to pass the lookup tables whilst the MAP helper function runs through the list of sales representatives. By the way, I did not perform a two-way lookup since both the sales table and the commission table have the same headers and the rates can be applied to the product array without further lookups.
Thanks for the formula, Peter B!!! I tried to implement your formula. This is what I tried:
= LAMBDA(sRep,sVal,lRep,rate, LAMBDA(rep,
LET(
repSales, FILTER(sVal, sRep=rep),
repRates, XLOOKUP(rep, lRep, rate),
SUM(repRates*repSales)
)
))(B18:B31,C18:F31,B10:B13,C10:F13,L57:L60)
But I did not get it to work. Any ideas?
@@excelisfun Yes. I have used two Lambda functions, so two sets of parameters are needed. These are not nested so they are read left to right.
= LAMBDA(sRep,sVal,lRep,rate, LAMBDA(rep,
LET(
repSales, FILTER(sVal, sRep=rep),
repRates, XLOOKUP(rep, lRep, rate),
SUM(repRates*repSales)
)
))(B18:B31,C18:F31,B10:B13,C10:F13)(L57)
The function is only capable of performing the calculation for a single sales rep, hence my second parameter is a single cell and not a range.
To get the result as a dynamic array, the entire function is placed within MAP. MAP takes each sales rep in turn and provides the name in place of the existing L57 reference.
I have added your cool example as a new file in the link area below video : )
Hi, love your channel, I appreciate your effort and hope you enjoy keeping your amazing channel!
But how about improving your sound a bit? maybe a new microphone that does justice to your pleasent voice? heh heh...
Anyway, keep on doing this~, and God bless you!
Sorry about the bad sound. What is wrong with sound? I already use best mic available...
@@excelisfun
Thank you so much for your reply. I am starstrucked.
I apologize for making you confused.
I had thought about this when I was watching your older videos. You have definitely superb quality audio and it has been very long also. It was my phone's speaker that needed trashing. (Your sound is excellent through my car audio)
Don't need to change a thing!
Hope you didn't waste your time watching youtube on how to improve your sound because you don''t need it.
Hey Mike ... is everything ok on the channel ? .... my comments are gone and your latest video is removed
You Tube has had really bad comment errors for a few years. All of use, you, me and many other Teammates have had comments mysteriously deleted. And RUclips has not fixed it... : (