Very much looking forward to this series! I think I know the answer from reading a couple of comments, but I'd love to know if there is a way to have these added to all workbooks by default.

Often, in the context of a large volume of data, we need functions that are optimal in terms of execution and runtime. Of course, the time depends on the amount of data as well as the method of solving. In this context, we need a time counter. In Excel, there are add-ons for this or VBA. Considering that I am not a fan of VBA, I looked for a solution within a LET(). Please let me know if this approach seems okay to determine / approximate execution time. =LET(ts, NOW(), matrix, MAKEARRAY(300000, 10, LAMBDA(r,c, SUM(r,c))), summ, SUM(matrix), tf, NOW(), HSTACK(ts, summ, tf))

the lambda to make a lambda..... 😀

Looking forward for more videos in this series.

Excited for next videos, thanks for the efforts!

Great video! thanks so much for the explanation... May I ask how to extract the date using REGEX? The date in a string can be in different pattern and languages, for example 4-19-2024, 4/19/2024, 4.19.2024, 19.4.2024, 4月19曰2024年, Apr 19, 2024, 19th April 2024.... (Note: 月 means month, 日 means Day, and 年 means year)

LAMBDA is very useful and together with dynamic arrays completely changes making of models. Only one thing is missing for me - there is no possibility to put comments in the code to improve readability. Yes, you can use some hack, but native support would be much better.

Diarmuid.. great introduction on Lambda functions. Look forward to your next video on how to create and save those custom functions. I have noticed lately that i have found myself writing the same Lambda functions over and over again to extract or clean data but now i have the need to save them and re-use them instead of writing them from scratch over and over . A few months ago, i had to create a Cartesian joint stitching 3 tables together using lambda helper functions and that was a drudgery. That will be my first custom function to save. Thanks

Can't wait to learn from your series✨ Thanks for sharing a valuable content , I always learn something new on each videos on your playlist 🚀

thank you mr. Diarmuid for the effort you put in your videos

I'm looking forward to the series, thank you for posting

wonderful ❤

I'm looking forward to this series too. I've already created my functions workbook with my most used functions. Looking forward to adding to it with new ideas. Thx Diarmuid.

😃

Looking forward to this series!

This is awesome! I always like to see how functions like LET and LAMBDA can be put to better use for practical applications. My field is Mechanical Engineering, and I have learned so much from your content that I apply in my work every day. The most used LAMBDA I have written so far is a simple crossproduct calculation. I know stuff like Octave and Python can do it natively, but my colleagues dont know how to program, so my work can't be documented for everyone to use, but Excel can.

Great!!

Wow, this is a university-level lecture. Thank you for sharing your knowledge, Diarmuid. I'm looking forward to the next video in the series.

Really impressive! I never expected to see the long division algorithm again in my life, though. I assumed it had been consigned to history, replaced by 'press ÷'. For really long multiplications Lori Miller used FFT convolution in Excel with success. That just speeds the calculation up.

Great presentation. Great solutions!

how can i combine a headcount model with a revenue model? Im thinking about how to model a P&L, with a accurate estimate for payroll, considering that with revenue variations we must have payroll variations + hiring and layoff costs.

During live I only made it through subtraction, partly because I enjoy one cell formula lambdas too much! But, I've been working on my own lambdas for the multiplication. I didn't know about GROUPBY and tried this for my multiplication, the formula isn't complete yet as the SUMIF doesn't work properly: =LAMBDA(a,b,LET(firstNum,revDigits(a,,,,TRUE),secondNum,TRANSPOSE(revDigits(b,,,,TRUE)),product,firstNum*secondNum,place,SEQUENCE(LEN(a))+SEQUENCE(1,LEN(b)),places,UNIQUE(TOROW(place)), ans,SUMIF(place,places,product),ans))(G152,H152) revDigits is taking the number and returning an array in reversed order. Is there a way to make this SUMIF work, or does it just not work in a big formula like this. If I separate it out into different spots on the sheet, the SUMIF works fine. Thanks! PS - just tried using GROUPBY and realized that it is Beta only for now. Time to upgrade my channel!

As a python programmer, I'm quite annoyed that I didn't even consider doing the python solution. (Though I just realized that my new computer wasn't signed up for the beta version so it wouldn't have worked anyway.)

Thanks for all brillant stuffs you provide. 😍😍 I love the accum Lambda that handle both add and subtract . I also love the diagonal sum for Multiply. I will apply both to my Lambda. And, Big Yes. I'm IN.

Damn those lambdas are impressive, although I can somewhat read and understand them I would struggle to be able to write something like that in 30 mins because it would take me far too long to figure out the logic! Cases like this are so good for improving though as it forces me to learn something new. Very good explanations of how your solution all works.

Ok I'm at 6 mins and have just seen the python solution. After spending considerable hours today trying to figure out carrying values in multiplication and iterating etc that is insanely frustrating! Live I did try python but typed like an excel formula which obviously didn't work!

This is indeed shocking to see how python makes it easy.

Second Like 😅😂

First like 🎉

I didn't know you could index match into an array result! Good to know

Please link of the case

=LAMBDA(income,taxTable, LET( tblMin,INDEX(taxTable,,1), tblMax,INDEX(taxTable,,2), tblRate,INDEX(taxTable,,3), MAP(income,LAMBDA(x,LET(a,tblRate*((SORT(IFERROR(--tblMax,x)))-tblMin),MROUND(SUM((a>0)*a),0.05)))))) or =LAMBDA(income,taxTable, MAP(income,LAMBDA(x,LET(a,INDEX(taxTable,,3)*((SORT(IFERROR(--INDEX(taxTable,,2),x)))-INDEX(taxTable,,1)),MROUND(SUM((a>0)*a),0.05)))))

Thanks, Dim! Glad you liked it, cause you tried like, EVERY case there is, right? hahahah Trying my best to make some fun cases! Thanks again for trying it and sharing it!

Dollar-to-dollar "method" but not for millionaires :)) =SUM(XLOOKUP(SEQUENCE(B1)-1, A4:A10, B4:B10,,-1))

One other thing re. the time spent improving a process... time spent improving one process also helps you make other future process improvements faster as well. It's process improvement improvement.

Excellent solution. Thank you.

The problem of graduated tax bands or, equivalently, that of banded commission rates is interesting because it admits array solutions that are not based upon past methodology. Operations like sorting and array shaping can be more than a presentational device; they can form a key element of the solution. In the present case, you form a 'high end of bracket' followed by the 'actual income'. Were you then to sort the income into place within the upper bounds, the entire calculation would follow without further tests on sign. =LAMBDA(thresholds, taxrate, [breakdown], [bandName], LAMBDA(income, LET( band, IF(ISOMITTED(bandNname), VSTACK(0, thresholds), bandNname), lower, VSTACK(0, thresholds), upper, SORT(VSTACK(thresholds, income)), split, upper - lower, txDue, split * taxRate, brkdn, HSTACK(band, taxRate, split, txDue), total, HSTACK("Tax due", "", "", SUM(txDue)), IF(breakdown, VSTACK(brkdn, total), SUM(txDue)) ) )) There is extra complexity because the function may be set to provide a simple result or a breakdown by band and is set to accept text descriptions of the bands (eg "Supertax"). I have also partially Curried the function so that it will process an array of incomes using MAP. I make no attempt to achieve brevity; for me there are no prizes for brevity. In fact, when I get back to development, I will probably at least double the formula length with a change control block, error trapping and help. I also think I exercised poor judgement in using the thresholds that are 1 fewer than the bands (0 would have helped). One can't win them all!

Genuinely flushed 15 mins down the bin this weekend doing exactly this. Then i wrote an overly complicated lambda 2 days later. Very impressed with @softwaretrain 's one also

=SUM((B1>A4:A10)*(B1-A4:A10)*(B4:B10-N(B3:B9)) =LAMBDA(income,brackLow,rat, SUM((income>brackLow)*(income-brackLow)*(rat-DROP(VSTACK(0,rat),-1)))

We have tax laid out like this too in Madagascar, so we've been digging quite a bit already on how to best calculate it. Here's our best version: Suppose the lower bounds are: 0, 10000, and 20000 The respective rates are: 10%, 15%, and 40% The income tax would then be determined by: =SUMPRODUCT((income - {0,10000,20000})*(income > {0,10000,20000}) * {0.1,0.05,0.25})

looking forward for the videos of Lambda!

Beautiful example of turning a common calculation into a reusable Lambda. Having an arsenal of lambdas available is quickly becoming the key to being competitive in Excel, not only for FMWC cases, but in life/work in general!

Thanks! A little over my head but worth learning and struggling through! Looking forward to the lambda series.

When I load my data it is painfully slow. loads like 10K rows per 10 secs and I need to load 13M. Yours loaded so fast....how?

Thanks for the introduction. I would like to point out 2 interesting things about the "Excel REGEX engine": 1. It supports local implementations, which means that "\w" corresponds to the set "[A-ZÀÂÆÈÊÔŒÛa-zçàâæéèêëïîôœûùüÿ0-9_]" in French, which is absolutely fabulous! 2. To test it, set REGEX101 "flavour" to "Python". I suspect this is also true for Spanish, German, etc., but I can't test it. If other people can testify?

That's caviar. Thank you!!!

I know you said it was inelegant, but I am blown away by the simplicity of CONCAT(IF(map1=x,map2,""))

Informative & useful as always! For the bracket stuff, if you want to do infinite bracket and bracket rules this violates the pumping lemma so regexand you need a language of CNF or equivalent strength - although this is worth checking as a that might just be DFAs (but off the top of my head it is all regular languages) For this specitic case where you want just a single pair you can just do an or of (123) and 123

Awesome as always, and reference to previous comment by HadynWiseman I am a 'purist' like Diarmuid but lack the experience and skill to always use those methods. At 19'38 can you confirm what was used to 'fill down' the answer for the other questions - thanks as always!

That is a mind-boggling use of lambdas to solve as well s making them more efficient. I'm curious how often you use recusion in your work? And when did the evaluate box start working for let formulas? Is that in the insiders channel still? Regardless, amazing s always Diarmuid.