This man not only construes countless niche examples and problems that the #spreadsheetlife throws at you, but he provides DIRECT file download links for every said example/video - all without clickbait attempts at monetization. Not all heroes wear capes; sometimes they wear an ExcelIsFun t-shirt.
@@excelisfun You are my Hero !!! I love you so much ! I will teach a course in finance and management accounting this coming months. I will try to help my students as much as you do ! Thanks a lot for all !
If Excel makes the users to be programmers like this, then what will be the job for programmers?! You are the only one who can deliver a lecture about 7 NEW functions within 17 minutes like a Marathon. Great job and thank you!
You are welcome! I am so excited about these new functions. They improve so many formulas that i use. I am glad that you enjoyed the video. What new ways do you have to use these functions? Your comment here was so funny: then what will be the job for programmers?! lol
Excellent presentation!!! Instant subscribe. MAKEARRAY is a favorite of mine. I like to use it when I'm aggregating with further computation, and want to produce multiple columns, but not necessarily the same shape as the inputs. I use the r (for row) parameter, to determine which calculation to output. Here's an example that references a spilled array at A3, just two columns (Division and Application): =LET( input,A3#, divs,UNIQUE(INDEX(input,,1),FALSE), CHOOSECOLS( SORT( MAKEARRAY( ROWS(divs), 3, LAMBDA( r, c, LET( thisDiv,INDEX(divs,r), SWITCH(c, 1,thisDiv, 2,COUNTIFS(INDEX(input,,1),thisDiv), 3,TEXTJOIN( ", ", TRUE, FILTER( INDEX(input,,2), INDEX(input,,1)=thisDiv ) ) ) ) ) ), {2,1}, {-1,1} ), 1, 3 ) ) This is its output, which shows each division on a descending sort by number of Applications, listing in the second column the names of applications associated with that division: Drama Bravo, Delta, Echo, Foxtrot, Golf, Hotel, India, Juliett, Oscar, Romeo, Victor Popover Kilo, Mike, November, Sierra, Tango, Uniform, Whiskey, X-ray, Zulu Cake Charlie, Lima, Papa Potato Alpha, Quebec, Yankee
Mike, these new functions are powerful. It will take very little time for you and all your clever disciples to come up with countless awesome uses. I am trying to figure out how to use these to create a stacked array out of disparate ranges. Stay tuned! (I hope!)
This video is criminally under-watched. So many times have I tried to emulate the ByRow functionality with other means. This would totally change the usability of dynamic arrays, by totally removing the need to create single cell formulas which need to be pulled down. While ByRow and ByColumn would be most used, people are under-estimating the usefullness of MAP, SCAN and REDUCE. Not to mention, combining REDUCE/SCAN with ByRow would be even more interesting. MakeArray could be insanely useful for those single cell reporting formulas. Also with these functions, higher order formulas are finally possible. With Lambda providing recursion and MAP/REDUCE providing a way for iteration, this can really provide a programmer friendly spreadsheet. Not sure when all this gets released for general public. But when it does, Excel won't be the same. Even today with dynamic functions, it is so advanced from just a few years ago.
You are totally correct!!!! Excel won't be the same. Thatnk you for your insightful comment : ) It is funny, I had a number of really negative comments where people said these functions are useless. I had to delete their comments because when people politely tried to point out the reasons why these are useful, the commenters just said they were dumb and did not get how pointless they were. It just shows you how uneducated mean people are excluded from the upper ends of what is possible.
@@excelisfun That's really unfortunate that a certain user-base will be resistent to change. Personally for me, with the advent of dynamic arrays and FILTER opened up so many opportunities. I was able to create live dashboards and then moved on to learning PQ and PowerPivot. I was one of those people who shrugged off Excel as being redundant, but with these new additions Excel is becoming into something which can't be ignored. Thank you for your kind comments. I really enjoy your content especially the videos about using dynamic arrays in unexpected ways. Excited to see all the new additions featured by you.
@@excelisfun We are all learning to effectively navigate a digital world where some fraction of the population are inclined to post negative and ignorant comments. Basically, we may just need to learn how to quickly spot those comments as useless and ignore them, so that content creators can spend more time on creating good content and trust that the rest of us will know which comments to ignore. I increasingly use the 'thumbs down' on a comment from a troll.
@@pmwilliams123 Thank you very much for helping with the thumbs down. That does push those comments to the bottom. With these comments, they kept posting ignorant troll stuff, so i had to delete... I am happy to hang out with you and have fun with Excel, Pete!
I have found uses for ISOMITTED, BYROW, BYCOL, and SCAN I can see where MAP and REDUCE wood be useful I have yet to find a situation where MAKEARRAY is better than SEQUENCE or other functions to create number arrays Unsurprisingly, BYROW and BYCOL can’t handle multiple results per row or column, like LARGE, SMALL, UNIQUE, and SORT I have found ways to include them within LAMBDAs Wonderful tutorial as always
@@excelisfun I’m not sure what’s going on but RUclips keeps deleting my reply Thank you for the kind words I was working with SCAN to create a running total count of items in a one dimensional array a few weeks ago. However, Excel crashed and I lost the formula. I can’t seem to replicate the methodology and the formula. I am hoping someone will see this and find the solution or help me out See comment below
Mike.... again: this is simply..... 😵🤯 M-I-N-D-B-L-O-W-I-N-G ! 🤯😵 ... I am not sure where to even begin processing all these new functions.... You have given us awesome homework! 🤘😝 #GoTEAM ! 🏆🏆
Awesome Mike! Seven times the fun with these seven new functions :)) Thanks for the examples and explanations. Looking forward to being able to play with these when released.. exciting times ahead! Thumbs up!!
Like I said on the Mr.Excel YT-channel: the scan and reduce functions are iterators that remind me of the List.Generate and List.Accumulate functions in PQ. I find the choice of names in Excel Classic not as intuitive as in PQ. But I do welcome these functions as they greatly simplify many of the custom LAMBDAs we’ve made and published on the Mr.Excel Message Board. And finally, as you have said in this video as well: they broke some barrier since these are the first instances where we have arguments of functions that are actually functions (LAMBDAs) themselves. We know this from PQ, but in Excel Classic that didn’t exist before. Interesting times (yet the Excel team could choose the names to some of these functions more wisely). Thanks for the video! :-)
You are welcome for the video, Teammate Geert. Be sure to check out some of the other comments that I made with interesting things. Always love your perspective : )
You can feel that aggressively hit on the backspace key for that copyright symbol hahaha Awesome video as always, my man. Love the channel, love the content. You're the first one I search on when I think on Excel.
I totally agree : ) : ) So much easier than MMULT. For me, I have used MMULT for so long and I am so used to it, so either is fine for me. But as a teacher, trying to teach MMULT to make row by row calculations in my stats and finance classes, is going to be so much easier now. These new functions make some complicated calculations, much more approachable : )
One thing I had to figure out on my own that was not well explained anywhere. BYROW and BYCOL work slightly differently than MAKEARRAY. BYROW and BYCOL pass the *actual content* as their argument to the embedded LAMBDA. That is, the BYROW function passes LAMBDA the content of the current entire row (as an array of there are 2 or more columns). BYCOL passes LAMDA the entire current column (also as an array if there's 2 or more rows). But MAKEARRAY does not pass content to the LAMBDA. It passes the row number and the column number of the current cell being calculated. You then use these in an INDEX or OFFSET function to acquire the desired content. This took me a few tries to understand that the "r" and "c" being passed to BYROW and BYCOL respectively, were not at all the same thing as the "r,c" arguments that MAKEARRAY passes to its LAMBDA. Essentially, the 2-dimensional cousin of BYROW and BYCOL is MAP. MAP passes the value of the single cell at the intersection of r and c.
You are right. The R and c that I uses were just variable names. And yes, MAKEARRAY is a very different function than BYROW and BYCOL. Thanks for your insight.
The byrow and bycolumn; how is it different than selecting the entire table with a blank column at the end and blank row at the bottom and then hit alt + "+" for autosum. It sums the rows and the columns automatically.
The difference is that with the cool method you described there is a single formula in each cell, with a BYROW or BYCOL and LAMBDA you can spill a set of aggregate calculations from a single cell. The vast majority of times, we use your method, but if you need to spill results from a single cell, like with single cell reporting, then that is where this advanced method is used.
I am not good at VBA so I do not teach it. I have joked about my not being good with VBA in 100s of my videos. I am surprised that you did not see one lol
Reduce() = List.Accumulate() from PowerQuery. =Reduce("(000) 000-0000", {"(", ")", "-", " "}, Lambda(state, current, Substitute(state, current, ""))). Lots of complex array transformations possible here! (Note: not able to test the above, since not insider, also could use column of #s as 1st argument of Reduce?)
In the comments I sense some closed minded reactions to these functions. That is a self defeating approach. Yes, these examples can be done in other ways but these functions represent a foundational change in Excel. "More to come" is a dramatic understatement. Lamda and Dynamic Arrays will become the core topics in the introductory course in excel. Touring complete anyone? MS knows it's either change or die (ie Lotus 123, Visicalc RIP) BTW How will you ever finish your book with MS changing Excel so quickly in such fundamental ways--it may need to be "loose leaf" !! LOL Good job.
I sooo agree: LET and LAMBDA and Dynamic Arrays will become the core topics in the introductory course in Excel. Although, Dynamic Arrays are taught from day one in a basics class and LET might be taught in middle, and LAMBDA is at end.
Yes, people may not see the power yet. But there are clearly some uses of these new functions that are significantly easier than alternative approaches. Making calculations row by row, or column by column are the biggest improvement. In addition, MAP seems less helpful because there are alternative array approaches which are just as easy. But I think for some advanced array formulas we can use MAP on the outside of BYROWS to significantly simply the formula. And SCAN is very helpful too, anytime we need a running total or other accumulation array.
Yes, the book is a big problem. I have been writing it for over 1.5 years and Microsoft added LET, LAMBDA and these new functions. Each time I had to go back and add a section. Then they changed the user interface, I updated some of the important screen shots, like for PivotTable connectors, but not all... BTW, I am on page 713, but have about 200 left... There are some really good parts in the book, that are not in other books, and the theme of the book (which is only for the hard core) is always trying to tie together everything, formulas, Standard PivotTables, Power BI and Power Pivot reports and visuals, DAX, Power Query, M Code - the theme has never been written, but the length of the book and the fact that I had a hard time during covid 19 are making it hard to bring all together and finish up. I hope I get it written in one month. Then it is off to the publisher for the long editing process...
@@excelisfun for me LAMBDA was a bit ahead of its time. It was like a workhorse that arrived at my farm without its plow. But with the arrival of these new functions and with the promise of other functions to follow we can finally put LAMBDA to doing real work. I guess (like every thing else these days) these plows were on back order!!
How would you create a column for a running balance alongside a column of deposits and a column of withdrawls? Both deposits and withdrawls are expressed as positive numbers.
Hi Mike. There is a mistake in formula N4. Instead of ISOMITTED function try ISBLANK and it should work perfectly =LAMBDA(Start;End;IFS(AND(ISBLANK(Start);ISBLANK(End));"Enter Both Start and End Amounts";ISBLANK(Start);"Please Enter Start";ISBLANK(End);"Please Enter End";TRUE;End/Start-1))(F4;G4). All, the best.
Hello Team. A big question: is it possible to use for instance the FILTER function (or any spilled function) to extract a table then add automatically a line at the bottom which will be to Total line calculating only some columns in the table filtered?
@@pupycron4302 Jonesing . You use that word too? I was jonesing for it the first few weeks it came out and I did not have them... They do add some big power to array formulas : )
On the question of the last guide Emt1748. Here is a method using the old functions: =SUM(IF(G12=fTransactions[Product],XLOOKUP(G12,dProduct[[Product]:[Retail Price]],2,0)*fTransactions[Quantity],0)-IF(G12=fTransactions[Product],XLOOKUP(G12,dProduct[[Product]:[Retail Price]],2,0)*fTransactions[Quantity],0)*IF(G12=fTransactions[Product],fTransactions[RevenueDiscount],0)) I appreciate your classrooms!
I so agree. ByCell would be so much better. And I do not get SCAN and REDUCE, why the heck would the function name not have accumulate in it? LOL (in a sad way)
@@excelisfun I'm guessing they've been taken from functional programming language. I think Haskell, or f# use these terms. I actually think they've made an error with all of these. I'm not sure that inserting the lambda into the function is at all intuitive. I'd far rather have seen more functions (SUMROWS, MAXROWS etc) than invoking lambda. Alternatively, given that you can only invoke the first parameter in the lambda, it just seems counterintuitive to have it there at all. Why not BYROW(SUM(A1:D3)) or even SUM((BYROW(A1:D3))? It's a little clunky for me, but the functions themselves are great. Apart from MAKEARRAY() which should be thrown in the sea. It's terrible.
@@ricos1497 These are great ideas and comments!!! I 100% wondered why BYROW(SUM(A1:D3)) does not work. Why do we even need the arguments? SUMROWS would have been a lot better.
@@ricos1497 You said: "I'm not sure that inserting the lambda into the function is at all intuitive." It is not at all intuitive. It is intimating and confusing. M Code is almost easier to understand than LAMBDA and the helper functions... At least with M Code, the logic is so clear once you get past the completely new syntax.
@@excelisfun yes! I completely agree about M code. I'm glad I'm not the only one that feels it could have been done better! I actually thought that with the advent of LET() that they were bringing a level of consistency to the two approaches (Excel and PQ), but I think this moves away from that. It's difficult, I suppose, because excel's history is row and column, whereas PQ is record and list, so perhaps they wanted to maintain that differentiation.
Great stuff, but WHEN is Lamdba slated for release? Cant wait to actually start using it, but I'm finding it very difficult to find out anyuthing from MS.
Given these new functions, they will want people t play around with them before release. That being said, it seems like sooner rather than later. I would be very surprised if they didn’t release it before the the end of the year
HELP. I haven't figured out how to do the following. Given a named range of prefix text and an array {} of suffixes I want to generate a single column of text where each prefix is joined with each suffix and a blank row in-between the prefix list.. ex. prefixes = {"a";"b";"c"} and suffixes = {"1";"2";"3";"4"}. I want the output to be {"a1";"a2";"a3";"a4";"";"b1";"b2";"b3";"b4";"";"c1";"c2";"c3","c4",""}. for bonus points have a variable to have arbitrary # of blank rows between the prefix lists. I've tried BYROW, LAMBDA, SCAN, etc.. can't et the output right
![Natanael Cano - Amor Eterno [Official Video]](http://i.ytimg.com/vi/EO4prCs6WpM/mqdefault.jpg)
This man not only construes countless niche examples and problems that the #spreadsheetlife throws at you, but he provides DIRECT file download links for every said example/video - all without clickbait attempts at monetization. Not all heroes wear capes; sometimes they wear an ExcelIsFun t-shirt.
That has my goal for 13 years here at RUclips: provide free Excel education for the world : ) : )
@@excelisfun You are my Hero !!! I love you so much ! I will teach a course in finance and management accounting this coming months. I will try to help my students as much as you do ! Thanks a lot for all !
If Excel makes the users to be programmers like this, then what will be the job for programmers?! You are the only one who can deliver a lecture about 7 NEW functions within 17 minutes like a Marathon. Great job and thank you!
You are welcome! I am so excited about these new functions. They improve so many formulas that i use. I am glad that you enjoyed the video. What new ways do you have to use these functions? Your comment here was so funny: then what will be the job for programmers?! lol
@@excelisfun Thank you again! Whatever we did using For...Next in VBA is now possible using ByRow/ByColumn inside Excel.
@@மின்விரிதாள்_விரிப்போம்_வாங்க So true. Isn't amazing : ) : )
WOW! This is a lot.
I need to go drink some water and think about this stuff.
I agree Oz .... 🤯🤯🤯🤯🤯🤯🤯... --->>> 😵😵😵😵😵😵
🤣🤣🤣
Excellent presentation!!! Instant subscribe.
MAKEARRAY is a favorite of mine. I like to use it when I'm aggregating with further computation, and want to produce multiple columns, but not necessarily the same shape as the inputs. I use the r (for row) parameter, to determine which calculation to output. Here's an example that references a spilled array at A3, just two columns (Division and Application):
=LET(
input,A3#,
divs,UNIQUE(INDEX(input,,1),FALSE),
CHOOSECOLS(
SORT(
MAKEARRAY(
ROWS(divs),
3,
LAMBDA(
r,
c,
LET(
thisDiv,INDEX(divs,r),
SWITCH(c,
1,thisDiv,
2,COUNTIFS(INDEX(input,,1),thisDiv),
3,TEXTJOIN(
", ",
TRUE,
FILTER(
INDEX(input,,2),
INDEX(input,,1)=thisDiv
)
)
)
)
)
),
{2,1},
{-1,1}
),
1,
3
)
)
This is its output, which shows each division on a descending sort by number of Applications, listing in the second column the names of applications associated with that division:
Drama Bravo, Delta, Echo, Foxtrot, Golf, Hotel, India, Juliett, Oscar, Romeo, Victor
Popover Kilo, Mike, November, Sierra, Tango, Uniform, Whiskey, X-ray, Zulu
Cake Charlie, Lima, Papa
Potato Alpha, Quebec, Yankee
Mike, these new functions are powerful. It will take very little time for you and all your clever disciples to come up with countless awesome uses. I am trying to figure out how to use these to create a stacked array out of disparate ranges. Stay tuned! (I hope!)
I can't wait!!!! Keep me posted : ) : )
This video is criminally under-watched. So many times have I tried to emulate the ByRow functionality with other means. This would totally change the usability of dynamic arrays, by totally removing the need to create single cell formulas which need to be pulled down. While ByRow and ByColumn would be most used, people are under-estimating the usefullness of MAP, SCAN and REDUCE. Not to mention, combining REDUCE/SCAN with ByRow would be even more interesting. MakeArray could be insanely useful for those single cell reporting formulas.
Also with these functions, higher order formulas are finally possible. With Lambda providing recursion and MAP/REDUCE providing a way for iteration, this can really provide a programmer friendly spreadsheet.
Not sure when all this gets released for general public. But when it does, Excel won't be the same. Even today with dynamic functions, it is so advanced from just a few years ago.
You are totally correct!!!! Excel won't be the same. Thatnk you for your insightful comment : ) It is funny, I had a number of really negative comments where people said these functions are useless. I had to delete their comments because when people politely tried to point out the reasons why these are useful, the commenters just said they were dumb and did not get how pointless they were. It just shows you how uneducated mean people are excluded from the upper ends of what is possible.
@@excelisfun That's really unfortunate that a certain user-base will be resistent to change. Personally for me, with the advent of dynamic arrays and FILTER opened up so many opportunities. I was able to create live dashboards and then moved on to learning PQ and PowerPivot. I was one of those people who shrugged off Excel as being redundant, but with these new additions Excel is becoming into something which can't be ignored.
Thank you for your kind comments. I really enjoy your content especially the videos about using dynamic arrays in unexpected ways. Excited to see all the new additions featured by you.
@@vasujan It is great to be on the Team with you, videos, learning, comments and back and forth dialog to be more efficient and have more fun!!!
@@excelisfun We are all learning to effectively navigate a digital world where some fraction of the population are inclined to post negative and ignorant comments. Basically, we may just need to learn how to quickly spot those comments as useless and ignore them, so that content creators can spend more time on creating good content and trust that the rest of us will know which comments to ignore. I increasingly use the 'thumbs down' on a comment from a troll.
@@pmwilliams123 Thank you very much for helping with the thumbs down. That does push those comments to the bottom. With these comments, they kept posting ignorant troll stuff, so i had to delete... I am happy to hang out with you and have fun with Excel, Pete!
I have found uses for ISOMITTED, BYROW, BYCOL, and SCAN
I can see where MAP and REDUCE wood be useful
I have yet to find a situation where MAKEARRAY is better than SEQUENCE or other functions to create number arrays
Unsurprisingly, BYROW and BYCOL can’t handle multiple results per row or column, like LARGE, SMALL, UNIQUE, and SORT
I have found ways to include them within LAMBDAs
Wonderful tutorial as always
Thanks for your succinct and accurate summary : ) Nicely said!
@@excelisfun
I’m not sure what’s going on but RUclips keeps deleting my reply
Thank you for the kind words
I was working with SCAN to create a running total count of items in a one dimensional array a few weeks ago. However, Excel crashed and I lost the formula. I can’t seem to replicate the methodology and the formula. I am hoping someone will see this and find the solution or help me out
See comment below
SCAN Example
Array Count Array Count
1 1 Apple 1
2 1 Banana 1
3 1 Orange 1
2 2 Orange 2
1 2 Apple 2
1 3 Orange 3
Mike.... again: this is simply..... 😵🤯 M-I-N-D-B-L-O-W-I-N-G ! 🤯😵 ...
I am not sure where to even begin processing all these new functions....
You have given us awesome homework! 🤘😝 #GoTEAM ! 🏆🏆
Go Team!!!!!!!
@@excelisfun minute 9:15 = is right now eye-opening RAD Mike. Woah. Taking a seat as I type this comment.
Awesome Mike! Seven times the fun with these seven new functions :)) Thanks for the examples and explanations. Looking forward to being able to play with these when released.. exciting times ahead! Thumbs up!!
Exciting times is for sure!!!!! Glad you liked this, Wayne : )
I knew it .... "ByRow" and "ByCol" would be amazing ...thanks Mike ....... i think it's the missing part of the array summition
Yes, those are THE best of all these functions. So useful : )
Wow !! Your eloquence is second to none. !! Fabulous presentation !! ✌✌🏆
Coming from you that is quite a compliment : ) : ) : ) : ) Thanks, Exceλambda!!!!
This is beautiful. Thanks Mike for this EXCELlent video.
You are welcome, fellow teacher : ) : ) : )
Like I said on the Mr.Excel YT-channel: the scan and reduce functions are iterators that remind me of the List.Generate and List.Accumulate functions in PQ. I find the choice of names in Excel Classic not as intuitive as in PQ.
But I do welcome these functions as they greatly simplify many of the custom LAMBDAs we’ve made and published on the Mr.Excel Message Board.
And finally, as you have said in this video as well: they broke some barrier since these are the first instances where we have arguments of functions that are actually functions (LAMBDAs) themselves. We know this from PQ, but in Excel Classic that didn’t exist before.
Interesting times (yet the Excel team could choose the names to some of these functions more wisely).
Thanks for the video! :-)
What’s with the audio?
@@GeertDelmulle FYI My audio working fine.
The choice of names is horrible. you have no idea from name that they are accumulators...
You are welcome for the video, Teammate Geert. Be sure to check out some of the other comments that I made with interesting things. Always love your perspective : )
You can feel that aggressively hit on the backspace key for that copyright symbol hahaha
Awesome video as always, my man. Love the channel, love the content. You're the first one I search on when I think on Excel.
Glad the channel helps!!
Thanks!
You are welcome! Thanks for helping support my efforts, Jeffrey : ) : ) Go Team!!!
Boom!Super Cool Formulas.Really Looking Forward To Try These Out When I Get Them...Thank You Mike :)
If you love array formulas, these function are just soooooo amazing!!!! Thanks, Super Cool Boom darryl!!!!!!
If you have 365 you can acces them in beta mode. They are fun and with a little practice, easy.
@@daeh49 You said that well: They are fun and with a little practice, easy.
Thanks Mike. It looks complicated.
You are welcome, Mark!! It is an upper level array formula topic.
Thank you Mike… the best is yet to come… keep up with excellent work💪🙂
Yes!!! I love your optimistic point of view : ) I will try to keep it EXCELlent ; )
this will EXCELlerate my work!
thank you sooooo much Mike! more power!
You are welcome soooooooo much!!!!!! : )
Thank you Mike, you do good work, keep it up! 👍 Learning a heck of a lot from you channel.
Great to learn about these new functions
They are pretty amazing. With all my array formula experience, it took me a whole day to finally wrap my head around it and find uses : )
Thanks for an amazing video ❤
Thanks Mike. Awaiting more videos with these 7 new toys. : ) : )
Yes, more is better, in this case : )
Cool! ByRow/ByCol + Lambda patterns are much better than MMULT~
I totally agree : ) : ) So much easier than MMULT. For me, I have used MMULT for so long and I am so used to it, so either is fine for me. But as a teacher, trying to teach MMULT to make row by row calculations in my stats and finance classes, is going to be so much easier now. These new functions make some complicated calculations, much more approachable : )
One thing I had to figure out on my own that was not well explained anywhere. BYROW and BYCOL work slightly differently than MAKEARRAY. BYROW and BYCOL pass the *actual content* as their argument to the embedded LAMBDA. That is, the BYROW function passes LAMBDA the content of the current entire row (as an array of there are 2 or more columns). BYCOL passes LAMDA the entire current column (also as an array if there's 2 or more rows). But MAKEARRAY does not pass content to the LAMBDA. It passes the row number and the column number of the current cell being calculated. You then use these in an INDEX or OFFSET function to acquire the desired content. This took me a few tries to understand that the "r" and "c" being passed to BYROW and BYCOL respectively, were not at all the same thing as the "r,c" arguments that MAKEARRAY passes to its LAMBDA. Essentially, the 2-dimensional cousin of BYROW and BYCOL is MAP. MAP passes the value of the single cell at the intersection of r and c.
You are right. The R and c that I uses were just variable names. And yes, MAKEARRAY is a very different function than BYROW and BYCOL. Thanks for your insight.
The byrow and bycolumn; how is it different than selecting the entire table with a blank column at the end and blank row at the bottom and then hit alt + "+" for autosum. It sums the rows and the columns automatically.
The difference is that with the cool method you described there is a single formula in each cell, with a BYROW or BYCOL and LAMBDA you can spill a set of aggregate calculations from a single cell. The vast majority of times, we use your method, but if you need to spill results from a single cell, like with single cell reporting, then that is where this advanced method is used.
Token of Gratitude!
Thank you so much for your continued support and tokens of gratitude!!! : ) : )
Hi Mike
I have learnt a lot from you. Can u just make a series on VBA or write a book
Thanks
I am not good at VBA so I do not teach it. I have joked about my not being good with VBA in 100s of my videos. I am surprised that you did not see one lol
Reduce() = List.Accumulate() from PowerQuery.
=Reduce("(000) 000-0000", {"(", ")", "-", " "}, Lambda(state, current, Substitute(state, current, ""))).
Lots of complex array transformations possible here!
(Note: not able to test the above, since not insider, also could use column of #s as 1st argument of Reduce?)
Beta mode is stable. I rarely have a problem. When I did, a repair was like running the weekly update.
In the comments I sense some closed minded reactions to these functions. That is a self defeating approach. Yes, these examples can be done in other ways but these functions represent a foundational change in Excel. "More to come" is a dramatic understatement. Lamda and Dynamic Arrays will become the core topics in the introductory course in excel. Touring complete anyone? MS knows it's either change or die (ie Lotus 123, Visicalc RIP) BTW How will you ever finish your book with MS changing Excel so quickly in such fundamental ways--it may need to be "loose leaf" !! LOL Good job.
I sooo agree: LET and LAMBDA and Dynamic Arrays will become the core topics in the introductory course in Excel. Although, Dynamic Arrays are taught from day one in a basics class and LET might be taught in middle, and LAMBDA is at end.
Yes, people may not see the power yet. But there are clearly some uses of these new functions that are significantly easier than alternative approaches. Making calculations row by row, or column by column are the biggest improvement. In addition, MAP seems less helpful because there are alternative array approaches which are just as easy. But I think for some advanced array formulas we can use MAP on the outside of BYROWS to significantly simply the formula. And SCAN is very helpful too, anytime we need a running total or other accumulation array.
Yes, the book is a big problem. I have been writing it for over 1.5 years and Microsoft added LET, LAMBDA and these new functions. Each time I had to go back and add a section. Then they changed the user interface, I updated some of the important screen shots, like for PivotTable connectors, but not all... BTW, I am on page 713, but have about 200 left... There are some really good parts in the book, that are not in other books, and the theme of the book (which is only for the hard core) is always trying to tie together everything, formulas, Standard PivotTables, Power BI and Power Pivot reports and visuals, DAX, Power Query, M Code - the theme has never been written, but the length of the book and the fact that I had a hard time during covid 19 are making it hard to bring all together and finish up. I hope I get it written in one month. Then it is off to the publisher for the long editing process...
@@excelisfun for me LAMBDA was a bit ahead of its time. It was like a workhorse that arrived at my farm without its plow. But with the arrival of these new functions and with the promise of other functions to follow we can finally put LAMBDA to doing real work. I guess (like every thing else these days) these plows were on back order!!
@@richardhay645 Love the analogy. You have such a great way with words. The ploughs were on back order lol^2
this is superrr ...
Glad you like this, Bugs : )
How would you create a column for a running balance alongside a column of deposits and a column of withdrawls? Both deposits and withdrawls are expressed as positive numbers.
Any word on when this roles out of beta? I don't have it in 365
Probably a few months. I had to wait a few weeks until I got them.
Hi Mike. There is a mistake in formula N4. Instead of ISOMITTED function try ISBLANK and it should work perfectly =LAMBDA(Start;End;IFS(AND(ISBLANK(Start);ISBLANK(End));"Enter Both Start and End Amounts";ISBLANK(Start);"Please Enter Start";ISBLANK(End);"Please Enter End";TRUE;End/Start-1))(F4;G4). All, the best.
Hello Team. A big question: is it possible to use for instance the FILTER function (or any spilled function) to extract a table then add automatically a line at the bottom which will be to Total line calculating only some columns in the table filtered?
Does anyone know when this is going to go be available to the general public?
Yes, the Microsoft Excel Development Team knows.
Probably a few months.
@@excelisfun Thank you. I'm jonesing for it!
@@pupycron4302 Jonesing . You use that word too? I was jonesing for it the first few weeks it came out and I did not have them... They do add some big power to array formulas : )
On the question of the last guide Emt1748. Here is a method using the old functions:
=SUM(IF(G12=fTransactions[Product],XLOOKUP(G12,dProduct[[Product]:[Retail Price]],2,0)*fTransactions[Quantity],0)-IF(G12=fTransactions[Product],XLOOKUP(G12,dProduct[[Product]:[Retail Price]],2,0)*fTransactions[Quantity],0)*IF(G12=fTransactions[Product],fTransactions[RevenueDiscount],0))
I appreciate your classrooms!
ByRow ... makes sense. ByCol ... makes sense. I guess ByCell would have been too obvious so Map ... ??? what are they thinking.
I so agree. ByCell would be so much better. And I do not get SCAN and REDUCE, why the heck would the function name not have accumulate in it? LOL (in a sad way)
@@excelisfun I'm guessing they've been taken from functional programming language. I think Haskell, or f# use these terms.
I actually think they've made an error with all of these. I'm not sure that inserting the lambda into the function is at all intuitive. I'd far rather have seen more functions (SUMROWS, MAXROWS etc) than invoking lambda. Alternatively, given that you can only invoke the first parameter in the lambda, it just seems counterintuitive to have it there at all. Why not BYROW(SUM(A1:D3)) or even SUM((BYROW(A1:D3))? It's a little clunky for me, but the functions themselves are great. Apart from MAKEARRAY() which should be thrown in the sea. It's terrible.
@@ricos1497 These are great ideas and comments!!! I 100% wondered why BYROW(SUM(A1:D3)) does not work. Why do we even need the arguments? SUMROWS would have been a lot better.
@@ricos1497 You said: "I'm not sure that inserting the lambda into the function is at all intuitive." It is not at all intuitive. It is intimating and confusing. M Code is almost easier to understand than LAMBDA and the helper functions... At least with M Code, the logic is so clear once you get past the completely new syntax.
@@excelisfun yes! I completely agree about M code. I'm glad I'm not the only one that feels it could have been done better! I actually thought that with the advent of LET() that they were bringing a level of consistency to the two approaches (Excel and PQ), but I think this moves away from that. It's difficult, I suppose, because excel's history is row and column, whereas PQ is record and list, so perhaps they wanted to maintain that differentiation.
🙏
Yes!!!!!!!!
Excuse me, I'm in Office insider Chanel but these functions are not in My Excel. I use LAMBDA yet. Do I have to wait only to see these new functions?
Great stuff, but WHEN is Lamdba slated for release? Cant wait to actually start using it, but I'm finding it very difficult to find out anyuthing from MS.
Given these new functions, they will want people t play around with them before release. That being said, it seems like sooner rather than later. I would be very surprised if they didn’t release it before the the end of the year
It's already out.
@@everstormz Are you sure? It's not showing in my work OR Personal install
They should have used the @ symbol in the way that the $ is used instead of the byrows and bycolum.
But makearray doesnot work with index,if we want to generate index as per input variables
HELP. I haven't figured out how to do the following. Given a named range of prefix text and an array {} of suffixes I want to generate a single column of text where each prefix is joined with each suffix and a blank row in-between the prefix list.. ex. prefixes = {"a";"b";"c"} and suffixes = {"1";"2";"3";"4"}. I want the output to be {"a1";"a2";"a3";"a4";"";"b1";"b2";"b3";"b4";"";"c1";"c2";"c3","c4",""}. for bonus points have a variable to have arbitrary # of blank rows between the prefix lists. I've tried BYROW, LAMBDA, SCAN, etc.. can't et the output right
I got LET functuon in my 🖥
I hope you got these functions too : )
Can you put your contact infor or email in your Contact page?
You can say what you want here : )
@@excelisfun I have a business invitation letter to you and need to send it in private...
Shame the lambda function doesn't prompt for any known arguments (e.g. Start,End)
1st comment
Another First Place Trophy!!!!! You better get a bigger shelf for all your trophies : ) : ) : ) : )
1st like
Thanks for the first like : )
I have a business invitation letter to you and need to send it in private...