Write your own Excel functions

[@marvinkoller5273]

I am so glad that a caliber like you shares his knowledge. A big thank you to you!

[@elbadlis]

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.

[@KO1967]

Thanks for the video. It made me take a look at a complex custom function written in VBA and consider converting it to a LAMBDA function. Long story short, on 10,000 rows the LAMBA version takes 10% as much time to calculate as the VBA version. That's huge.

[@DimEarly]

That's great - congrats!

[@Nicolas-gt9qf]

Also with VBA you can only see the names of the arguments you can pass to the function with the use of Ctrl + Shift + A which is not very ergonomic for the end user, well you can technicaly also use the Fx API but it's not great either. Or you have to open the VBA editor to see what the function wants. Great video.

[@LyubomirRusanov]

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.

[@DimEarly]

I’m always glad to meet a fellow LAMBDA fan! : ) It’s not *exactly* native, but take a look at the Advanced Formula Environment (I’ll also talk about it later in the series). It’s helpful for development, and allows for in-line comments (although I think they don’t get saved in the name manager, so they’re only visible in the AFE). If you use LET (another topic I’ll come to later in the series!), you can also use comment variables, as in LET(calc, a+b/c, calc_note, “b/c is the performance adjustment”, …

[@Mohamed77779]

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

[@andrew_ngai]

Thanks for sharing :) 14:10 interesting point there, I think to make that a reality people will need to document their lambdas (to counter the benefit of transparency when you can see exactly how things work), which isn't a hard thing to do but requires discipline

[@DimEarly]

Totally agree! I'll be talking more about documentation later in the series... I've seen some pretty interesting approaches people have taken to it.

[@michaelbrown8821]

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.

[@jazzista1967]

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

[@DimEarly]

It’s definitely worth having some good ways to reuse them, and I will talk about that down the line (but I’m planning to start very basic!). For me, I keep a file with LAMBDAs I regularly use in my pinned file list so I have it whenever I need it - but I’m starting to be won over by Team GitHub / Gist as a better way of sharing with others.

[@jazzista1967]

HI Diarmuid! Thanks for your reply. Are you going to walk us through on how to save the Lambdas in Github? I guess you need to create an account for that. Anyway, I am anxiously waiting for your next video. Regards

[@SN-us5se]

Looking forward for more videos in this series.

[@playlists9782]

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

[@ChristianMbolanantenaina]

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 🚀

[@DimEarly]

Thanks Christian, that's always so good to hear!

[@joshritchie7038]

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.

[@DimEarly]

Thanks Josh - it's always good to hear that someone has found my stuff helpful!

[@mohammedbaydoun9464]

Excited for next videos, thanks for the efforts!

[@HadynWiseman]

Looking forward to this series!

[@nemoyatpeace]

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.

[@DimEarly]

It might be possible to have them appear in any new file you create (you can change the template that new files open from, so maybe you could add that), but I doubt you’d want to have them injected into every existing file you open even if they’re not being used. There are some pretty zippy ways to import them though…

[@tamersalem7542]

wonderful ❤

[@ValyGreavu]

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))

[@DimEarly]

Yes! I think I learned this technique from Bo Rydobon or Bhavya Gupta originally. Since the running times are usually pretty fast, I think the way Bo usually does it now is something like this: LET(st, NOW(), mainCalc, , HSTACK(mainCalc, TEXT(NOW()-st, “s.00”)) That way it gives you the result and the run time in seconds to 2 decimal places (I think that’s the limit of the accuracy).

[@ValyGreavu]

@@DimEarly thank you for detailed message. Yeah, accuracy seems to be a issue...

[@andrew_ngai]

@@DimEarly I used this the other day. For the accuracy issue, I suggest just asking Excel to calculate it many times, e.g. instead of do "BYROW(SEQUENCE(10000), LAMBDA(x, ))"

[@DimEarly]

Haha - my solution to the accuracy issue is not to worry about

[@richardhay645]

Great!!

[@CraigHatmakerBXL]

😃

[@sledgehammer-productions]

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

[@DimEarly]

Haha - have you seen it? It’s coming… but I thought I would make for an intimidating start to the series! 😂