What is LAMBDA in Excel

[@TP014563]

This will be like Lego. Building blocks to construct anyway you want. This is very interesting especially the sharing feature. People sharing thier workaround issues for simple or complex tasks in excel will have limitless potential!

[@controlsgirl]

absolutely fantastic walkthrough!!! I love the way you built it up from the start of the very basic functions.

[@AccessAnalytic]

Glad you liked it, thanks for the great feedback

[@peterbrandberg1816]

I have been looking forward to this. For my own part, I will use this for complicated formulas that my less experienced colleagues can work with. But it feels like there will be lots of more uses in the future. Thanks for a great video. 👍

[@AccessAnalytic]

Yes Peter, this does open up a whole new set of functionality in addition to just making long formulas simpler to use and re-use. Some of the LAMBDA helper functions that are already out or are on their way are going to be VERY useful

[@mandypaulissen]

Oh My!! This is mind boggling!!!!!! 💥💥💥

[@AccessAnalytic]

Yeah it’s a shift what’s now possible

[@sherlock27]

Wow! This feature is big! Thanks for sharing

[@AccessAnalytic]

No worries

[@AnkitGupta-cn1zd]

I really appreciate the way you simply the stuff. Happy to learn ;)

[@AccessAnalytic]

Thank you Ankit

[@meniporat3527]

Nicely explained. A better method to comment out is by using the "/*" (slash-asterisk at the beginning of the comments block) and "*/" (asterisk-slash) to end the comments block. The advantage: you don't need the double-slash "//" in each and every comment line. /* bla bla bla */

[@AccessAnalytic]

Thanks Meni

[@karenadamson5243]

It looks fantastic 😊 thank you for this tutorial 😊 I just need to upgrade at some point to actually be able to use the Advanced Formula Environment, it's there, but upgrade is required!

[@AccessAnalytic]

You're welcome Karen

[@jazzista1967]

Great examples Wynn. I will try this at work. I hope to be able to download the Advanced Formula Environment (AFE). I was able to download the ad in in my personal computer. Thanks

[@wayneedmondson1065]

Hi Wyn. Awesome news and thanks for demonstrating both your LAMBDA example and the AFE. Agree w/Geert below that it should eventually be a built-in. In fact, it would be nice to have a more common coding environment across LAMBDAs, DAX, M, VBA (not likely), etc. Right now, each has something different to learn and navigate. Maybe someday they will all be a little more common. Thanks and thumbs up!! PS - Love to see some future vids on recursive LAMBDA examples.

[@AccessAnalytic]

Yes this AFE is a step in the right direction, I like that SHIFT Enter has been adopted for new line and Ctrl Shift + zoom is consistent with Power Query. I don't know if I'll be the one doing recursive Lambda demos (never say never) but some of the other MVPs are lapping it up 😁 and producing some amazing ideas. Lots of good content will start to flow now it's out in the real world. However, it's not backwards compatible, so I won't be using it until it hits the Semi Annual Channel - maybe in August this year - maybe...

[@peterbartholomew7409]

@@AccessAnalytic Hi Wyn, I have just noticed your comment above. I suspect that recursive Lambdas were something of a seven-day wonder; interesting but pretty much gone before they were ever adopted. In most cases, SCAN or REDUCE will do the job better and recursion is mainly useful situations in which you cannot calculate how many steps are needed ahead of time.

[@AccessAnalytic]

Fair comment Peter

[@arbazahmad7177]

Very clear explanation. Thank 😊

[@AccessAnalytic]

You’re welcome Arbaz, thanks for taking the time to let me know

[@filipwinski4219]

Very interesting. Excel formulas are now much closer to DAX. The question remains whether users will accept it - especially in financial modeling, where there is a very static, old-school and technologically outdated approach, such as the FAST standard.

[@AccessAnalytic]

Yes Filip, the technology is changing rapidly, even 2 years ago my colleague Jeff did a video on using Dynamic Arrays to build a financial model kzbin.info/www/bejne/oaXUg32KjMp3a9E

[@wazzadec16]

I don’t understand why let was called let when declaring variables in Dax is Var seems kind of inconsistent. I suppose they used it since its based on VBA.

[@AccessAnalytic]

@@wazzadec16, I agree, I did advocate for using VAR to be consistent with DAX, Power Query also uses let, so I'm guessing there's some programming history to it.

[@lorenzoladejobi8701]

Very educative. Wyn you are a great Teacher ! . Thanks.

[@GeertDelmulle]

My thoughts: conceptually really great! But why as an add-in? Why not integrate this into the Name Manager editor and/or the formula bar + Name Box? The latter would be the best way to go, IMO. BTW: at work I can’t get the ‘AFE’ because add-ins are prohibited. This strengthens my point for integration.

[@AccessAnalytic]

Agreed Geert, I’d imagine the formula bar overhaul is a fundamental change, so baby steps it is. It will get built in one day but they can get loads of feedback and make improvements before going all in and integrating it. Frustrating for those that can’t get the addin though

[@SamsungA-wv5bh]

Amazing ! Do release more tutorials of lambda

[@AccessAnalytic]

Thanks, will do

[@jeongwonc6462]

Excellent! My challenge will be remembering a name of Lambda functions if I have a hundred of them 😅

[@shirleymoreman6725]

Thanks for the great video Wyn. I can see uses for this, but I have to be sooooo careful when I'm developing things for my clients. I have one or two that are still on Excel 2010!!! Can't throw away the VBA just yet I guess.

[@AccessAnalytic]

Yep it’s a problem. I had a client with 365 but hadn’t updated for 2 years so they still didn’t have XLOOKUP a few months ago

[@shirleymoreman6725]

😂 Makes you wonder why they bothered with a subscription if they never did the updates!

[@garylhaas2005]

awesome - watching a geek do stuff is educational - your video showed me the flow to use - voices in my head tell me Lambda will be available in web version of Excel

[@AccessAnalytic]

Thanks... I think 😏. Yep already available in web version

[@jagatkrishna1543]

Thanks SIR 🙏💕 God Bless You 🙏💕

[@vincennes1234]

Thank you very much for this video. it's actually awesome I am French and my Excel is of course in French. When I use "Advanced Formula environment", my formula does not appear in the name manager. I use the English terms in my formula so that my formula is valid (for example SUMIF in French it is SOMME.SI). I guess that's the reason for this failure. Do you know if there is a way around this? thanks again

[@AccessAnalytic]

I’m not sure sorry, maybe my friend Frédéric will do a video soon kzbin.info

[@vincennes1234]

@@AccessAnalytic Thank you very much for your answer I contacted him saying that I came from you. Have a good day

[@patrickschardt7724]

I always have wanted SEARCH and FIND to return 0 if it they don’t find the search criteria and a number greater than 0 aka position if they do. It would formulas a little easier. Anyways, great tutorial. I am excited to try the advanced formula editor out

[@martyc5674]

There’s an idea for your first lambda…

[@patrickschardt7724]

@@martyc5674 good idea. I’ve made a few LAMBDAs in the beta. This will be my first real LAMBDA I also think they should be one function and just have an optional argument to set case sensitivity, with the default being not case sensitive

[@AccessAnalytic]

@@patrickschardt7724 , agreed. I know the Excel team are conscious of this and new functions in the future will adopt that optional argument approach. unfortunately once a function is out in the real world it's virtually impossible for the Excel Team to change as it would break folks existing spreadsheets.

[@SamsungA-wv5bh]

You can even store your 'compare two data sets' formula inside lambda right?

[@AccessAnalytic]

Yep, I’ve added it to my GIST already 😀. Although the hyperlink functionality doesn’t work if inside a lambda

[@GrainneDuggan_Excel]

Thanks for detailing all the steps. If I write a LAMBDA in my spreadsheet, can a user on an older version of Excel use it or see the result if I used it?

[@AccessAnalytic]

No, that's a good point Grainne, this will not work on Pre-Lambda versions of Excel. I should have called that out as it's an important consideration. I'll add that comment to my description notes now. The end user might still see the results when they open the file, but as soon as they change any value that the LAMBDA references they will get ?NAME error

[@GrainneDuggan_Excel]

@@AccessAnalytic thanks for the clarification Wyn. Not surprising that it isn't backwards compatible. But at least those of us on M365 have it!! FYI, I played with your sample LAMBDAs and had great fun. Thanks 😊

[@AccessAnalytic]

@@GrainneDuggan_Excel Yes, the issue now is though that I have clients with 365 where their IT departments haven't updated for 2 years!! So all 365 is not equal

[@alanmonaghan9194]

@@AccessAnalytic My work is still stuck on Office 2016 :-(

[@AccessAnalytic]

@@alanmonaghan9194 - all too common sadly

[@ToddBeal]

@Access Analytic I want to see my current named formulas show up in the Advanced Formula Environment, but the plugin won't sync in that direction: only from AFE to Name Manager, not Name Manager to AFE - looking forward to this capability in the future. This feature would be a great step to eliminate VBA coding as much as possible and, instead, write a formula using the advanced features in AFE. Thank you for this video.

[@AccessAnalytic]

Hi Todd. My named formulas sync both ways. Sounds like a bug. Click on the Feedback option in the top right of the AFE or email [e-mail]

[@shirleymoreman6725]

I had the same problem when I first looked at this, but reinstalled the AddIn today and it seems to work both ways. I did an Excel update yesterday so could have been a bug that's now fixed.

[@AccessAnalytic]

Thanks for the update Shirley

[@brhju74su1ijjoi]

Hi, I'm from Brazil, I discovered this supplement a month ago, but I couldn't understand the explanation. how the editing of the lines works there in the Advanced Formula Environment editor so I created a lambda formula it calculates the BMI the body mass of a person =LAMBDA(HEIGHT; WEIGHT; WEIGHT / (HEIGHT^2)) how do i enter the lines in the Advanced Formula Environment editor?

[@AccessAnalytic]

Hi Bruno, you will need to use commas rather than ; Only English and US style is currently supported. Is that the issue?

[@brhju74su1ijjoi]

@@AccessAnalytic I used the commas, the error still persists, I wrote the formula in English, the error persists

[@AccessAnalytic]

Are you clicking the button to sync your calculation with the name manager? Does a simple measure like my Ass2cells example work for you?

[@controlsgirl]

I loooooooovveeee this!!!!!

[@AccessAnalytic]

Awesome 😆

[@IvanCortinas_ES]

That's ok but it's not useful for me. I work with an spanish version Excel (for example, FILTER = FILTRAR, ISNUMBER = ESNUMERO, SEARCH=HALLAR...) This causes the synchronization with the formula manager to not work, since it does not understand anything other than function names in English. A pity! I think I will continue to work with the Lambda function in a conventional way. Thank you anyway.

[@AccessAnalytic]

I’d recommend you provide feedback here Iván. www.microsoft.com/en-us/garage/profiles/advanced-formula-environment-a-microsoft-garage-project/

[@IvanCortinas_ES]

@@AccessAnalytic Thank you Wyn!

[@AccessAnalytic]

I’ve also asked the Excel team to clarify the language support situation and plans

[@IvanCortinas_ES]

@@AccessAnalytic That is something important (the compatibility between different languages). Thank you for your intervention. Certainly the advanced editor is a new present/future step towards a programmable Excel. What seems clear is that the Lambda function is here to stay. Thank you!!!

[@Ganja1974]

No more VBA based UDF's!

[@AccessAnalytic]

Absolutely, Vasif ( well for backwards compatibility reasons UDFs will be around for many years to come )

[@jerrydellasala7643]

Great Video! QUESTION: I have a bunch of LAMBDA functions. Some behave just like a function - that is when I type in "=" and the name of the LAMBDA and hit TAB when highlighted in Intellisense, a parenthesis is automatically added to the end of the LAMBDA name, and in the Intellisense list it appears with an "Fx" in a circle icon to the left of the name. This LAMBDA which is named _Bround behaves this way: "=LAMBDA(num,pres,IF(MOD(num,1)=0.5,MROUND(num,2),ROUND(num,pres)))" Others do not do this. When hitting TAB to select it no parenthesis is automatically added, and it has an icon of a 3x3 grid with the two middle left cells filled in blue kind of like a table. This LAMBDA named _CellFormula behaves this way: "=LAMBDA(fCell,IF(ISFORMULA(fCell),"Formula in "&ADDRESS(ROW(fCell),COLUMN(fCell),4)&": "&FORMULATEXT(fCell),""))" Can you tell me what I can do to the second LAMBDA to get it to behave like the first - like a formula? Incidentally, I use an underscore as the leading character of a LAMBDA's name to get to it quickly and easily, and also typing "_" automatically lists all my LAMBDAs in Intellisense. Also, please forgive if I've misspelled "Intellisense"! Thanks!

[@AccessAnalytic]

Hi Jerry, yes that’s a bug the Excel Team are looking into. I’m a fan of the underscore too, especially when writing DAX, the parameter names do show up for the end user but I think underscore is not that noticeable so I’ll be using it in the future 👍🏼