Wonderful to hear! You're very welcome, Stephanie!

Thanks for your kind words, Ivan!

You're very welcome!

Woo hoo! Congratulations. So glad I could help, Ivan 😀

Glad it was helpful!

Thanks so much! Glad you're looking forward to tinkering with LAMBDAS.

Thanks, Ricky!

Nice! It's certainly much easier than VBA.

Glad you think so! 🙏

Better late than never 😊 have fun writing your own LAMBDAs!

Glad it was helpful 😊🙏

Thank you too!

😁

So pleased you found it helpful, Benson! LET is generally available. LAMBDA is still on Office Insiders at the moment. I don't have an ETA for it.

Glad you like it, Darryl!

Aw, you're too kind 😉

Sure does! Have fun writing your own LAMBDAs 😊

Thank you!

Thanks for your support, Henry!

Glad you like them!

Glad it's helpful! Best wishes.

Thanks for the suggestion 😊

Cheers, Chris. Powerfully mind bending :-)

Nice :-) Daniel Ferry taught me that MEDIAN trick. It's a keeper :-)

Thank you!

Great to hear, 🙏

My pleasure, Rasmus!

Glad you like it, Hazem!

Thanks for watching, Patrick! Yes, avoiding function names in your LET variable names is recommended, as per my VAL, UP and LOW example.

@@MyOnlineTrainingHub this probably true for the original name deranged too and just good practice to avoid confusion. I have finished some of the functions and I am now refining them (shorter, less variables, faster, etc). For others, I am still working on them. I will share the better ones when I am done

Great. I'm looking forward to seeing what you've created. I struggled to find a good example for recursive LAMBDAs.

@@MyOnlineTrainingHub stripping characters from a string and appending n tables are great for recursive LAMBDAs

@@MyOnlineTrainingHub Here is a TEXTCOUNT function. It takes a single delimited text string or a range of delimited text strings (Example A1, A1:A3, A1:C1, or A1:C3) and returns the number of values in each string. Dividing by the delimiter length enables multi-character delimiters like ", " Checking for non-zero length strings allows for blank cells (otherwise an incorrect count of 1 would be returned). =TEXTCOUNT(Text, Delimiter) =LAMBDA(Text,Delimiter, LET(T, Text, D, Delimiter, TLen, LEN(T), TLenNoD, LEN(SUBSTITUTE(T, D, "")), DLen, LEN(D), Result, (TLen-TLenNoD) / DLen + (TLen>0), Result))(Text, Delimiter)

Most welcome!

My pleasure :-)

Thanks 😄

It's my pleasure 😊

My pleasure, Wayne. Yes, they're working on the lambda authoring experience.

Glad you think so, John!

Currently LAMBDA functions are only present in the active workbook. You would have to copy it to each workbook you want to use it in.

Glad you liked it, Yulin :-)

Certainly opens new avenues in the worksheet.

My pleasure!

Thank you! I'm not sure what you mean by 'use a sheet as a formula'.

@@MyOnlineTrainingHub So for example, I have sheet 1. It has 5 'variables' that then affect a big table with calculations with sums and a final result at the end. On sheet 2 I want to be able to have a table of those variables and a column that gives me the result for the corresponding set of variables if they had been input into sheet 1

A little clearer, but still not 100%. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

Not sure why you're getting that, Bernard. You can post your question and Excel file on our forum and I'll take a look. www.myonlinetraininghub.com/excel-forum/

I don't know Arabic, sorry.

Glad you think so, Harsh!

Glad you liked it!

Thanks, Ivan! No idea when you'll see it generally available in 365.

A couple of reasons: it's more accessible to users not familiar with VBA, it also doesn't require the file to be saved as a .xlsm and then macros enabled upon opening.

Hi Dave, the Excel journal was a gift from Microsoft to us MVPs at one of the annual summits I attended a couple of years ago. Not sure you can buy them anywhere, sorry.

LAMBDA is only available with Microsoft 365, so you would need to purchase a Microsoft 365 license.

Thanks, Emre! I'd imagine you can call a lambda with VBA.

LAMBDA is currently only available with Microsoft 365.

Yes, I'd have thought so.

Thanks, Ubaidillah!

Welcome :-)

Hi Vivek, you can already to this without LAMBDA, so I'm not sure why you would try to write your own versions of these functions? If you did try to write these formulas as a lambda, they would likely be very inefficient because your lambda wouldn't benefit from the efficiency features built into how these functions evaluate in the Excel calc engine.

@@MyOnlineTrainingHub Hii Mynda, =LAMBDA(a,LOOKUP(2,1/(a""),a))(A:A) I used this formula to find the last non blank cell value in the column A & it worked great. Now I simply need to select the column reference after giving a name to my Function...Thanks a lot. 😀

the VALUE.D,VERSION.D are named columns in my model, also SEGMENT.D

Hard to say without seeing your file. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

Hi Mike, LAMBDA is only available with a Microsoft 365 license. There's no way to get it in Excel 2019.

Cheers, Dave!

There is a new LAMBDA editor coming, but perhaps the biggest upside to LAMBDAs is that they work in Excel Online, whereas VBA doesn't and never will.

Hi Rob, I'd expect a few months at least, but I honestly don't have an ETA for when LAMBDA will be generally available.

I believe that Microsoft are releasing a new non-subscription version of Excel later this year (Excel 2021), I wonder if they'll coincide with that release? It's a long way off being ready in my opinion. The recursive LAMBDA has serious limits, they don't seem to clear in memory after each recursion, and there's a limit to the number of recursions (divisible by the number of parameters, making it significantly less with more parameters). I don't think the use of name manager can be sustained, Lambdas should have been enterable by selecting the fX button in the formula bar and typing in a name. That'd have been far better, and I see no obvious reason for having a formula called lambda in itself. It is awesome though!

@@ricos1497 At the moment, I think the (number of recursions) x (the number of parameters) < 1024 How do you know that the memory is not cleared following each recursion? That would seriously affect the effectiveness of 'tail end recursion' strategies which aim to control the stack size. I think some of the decisions regarding the new functionality were determined implementation convenience and consistency with the existing calculation framework. p.s. I do not consider myself in any way knowledgeable on functional programming, it has all come as a rather rude awakening after years of familiarity with an imperative programming style (the Excel sheet itself having been just about the only exception).

@@peterbartholomew7409 I don't know if it clears memory or not, I misspoke. I should have said, it's like the memory isn't cleared. You're right, the 1024 is divided by the number of parameters, which is a limitation. However, that may just lead to better functions being written. I've dabbled in functional programming, and have tended to ape FP when writing in VBA over the last few years. It's a different thought process, but quite an elegant and reusable one! The hardest part is splitting your new LAMBDA functions into their [LAMBDA] functional parts. With the advent of LET without LAMBDA, I was writing screeds of iterative "coding" and now I'm going back through that and saying "what is this line doing" and then assigning a function to that line. It leads to a much more readable and intuitive function in the end I think, a lot more like a traditionally nested formula in Excel albeit the nesting is of lots of new LAMBDA functions that didn't previously exist. I've just created a textsplit function that I'd originally done in let, and it now nests a handful of other functions instead. It's interesting.

Except for UDFs you need to save the file as .xlsm and macro enabled workbooks are not allowed in some organisations. They're also more complicated to write. LAMBDA offers a relatively easier entry point to writing custom functions.

UDFs are slower than native formulas and don't use multiple CPU cores (as well or at all). As soon as it gets a little challenging, UDF are a PITA to use, esp. compared to data handling on import (PowerQuery), Analyzing (PowerPivot and DAX)...