This is gold! I just applied the automatic LAMBDA creation you demonstrate (4:45) to a function that had a half-dozen helper cells to get from the single cell input to the output value. A lot of interim values were repeated in every cell. I was surprised and delighted to see that the auto-generated function included the concise set of LET statements to remove all of the duplicated calculations. I was impressed.
@MyOnlineTrainingHub
Жыл бұрын
So great to hear 😊
@xaquison
Жыл бұрын
Excellent video! Thanks for sharing. There is so much potential using AFE.
@MyOnlineTrainingHub
Жыл бұрын
Totally agree!
@RenierWessels
Жыл бұрын
Great video. Thanks for sharing Mynda! Amazing to see a couple of things have already changed since you've released this video. The Excel team is busy indeed.
@MyOnlineTrainingHub
Жыл бұрын
They sure are 😊 Glad you enjoyed it!
@peterbartholomew7409
Жыл бұрын
Mynda, that is the best reference documentation on the AFE that I have come across (though it may be just a case of not knowing where to look)! I had only recently encountered the F2 renaming that saves a lot of grief. The grid to Lambda has some oddities, why does = SUM(variableX, variableY) - variableZ appears as =LET( subTotal, SUM( MAKEARRAY(2, 1, LAMBDA(i, j, CHOOSE((i - 1) * 1 + j, variableX, variableY))) ), SUM(subTotal, -variableZ) ) Despite that, I used the functionality yesterday to open up a circular reference (one of these horrible models where the interest calculation depends on both closing and opening balances) and then had the means to iterate the calculation towards convergence.
@MyOnlineTrainingHub
Жыл бұрын
Thanks for your kind words, Peter! The MAKEARRAY function is used when one of the references is to a range of cells. Seems cumbersome, I agree.
@darrylmorgan
Жыл бұрын
Hi Mynda!Great Tutorial.I Have Used Advanced Enviroment A Few Times And Found it Really Impressive...Thank You :)
@MyOnlineTrainingHub
Жыл бұрын
Great to hear, Darryl 🙏
@patrickschardt7724
Жыл бұрын
Wow these are great improvements. Once they add in things like auto parenthesis insertion and mouse selection, it be just about perfect
@MyOnlineTrainingHub
Жыл бұрын
Yep 😊
@mirrrvelll5164
Жыл бұрын
Such a great demonstration of Advanced Fx editor! Wow!
@MyOnlineTrainingHub
Жыл бұрын
Thank you!
@sunnybaggu785
Жыл бұрын
Thank you for this content. Your explanation is very clear. Very few videos AFE is available in KZitem.
@MyOnlineTrainingHub
Жыл бұрын
Thanks so much 😊
@MrSweck71
Жыл бұрын
I love this tool! The sad thing about it is that our IT-admin has blocked Office Store to avoid any "add-in wild west". I had tried it at home and decided that I wanted it, so I asked for it and got it installed. But not many of my colleagues are playing with Excel at home and will therefore never see it... and even if I show them, they will still never bother to get it since they can't do it by themselves. I hope that one day it will be included as a standard tool, like the VBE. Many people want it even though they don't know it yet.
@MyOnlineTrainingHub
Жыл бұрын
Glad you're making use of it. I expect it will eventually be a built-in feature.
@MrSweck71
Жыл бұрын
@@MyOnlineTrainingHub The other day another strength struck me, when I noticed that it lets me enter English version formulas in my Swedish version Excel. That has been annoying for a long time... reading about Excel on the internet and then having to dig through the functions and search for the Swedish equivalents to be able to use it. A "workaround" became to enter the English formulas using the Immediate Window in VBE and then see what the spreadsheet translated it to. But with the AFE I can do it without having to leave the worksheet view,, like having two different Excel versions at the same time. So much simpler and I love it even more than I did before. :)
@robalan9975
Жыл бұрын
Wow. This is a very interesting add in to be aware of. Thank you very much for the tutorial. I can see lots of potential for this in my work. Happy holidays!
@MyOnlineTrainingHub
Жыл бұрын
Glad it was helpful! Happy holidays to you too 😊
@CeliaAlvesSolveExcel
Жыл бұрын
Extremely useful, Mynda! Thank you for this. Question: how do you type in the lambda character?
@roseventura1711
Жыл бұрын
Type 039B (for upper Lamba) or 039b (for lower Lambda), then [Alt] X. Or you can import Lambda symbol (Insert > Symbol)
@CeliaAlvesSolveExcel
Жыл бұрын
@@roseventura1711 thank you :)
@peterbartholomew7409
Жыл бұрын
I did it originally by using Insert > Symbol and picking it up from the Greek character section. Next I went to Options > Proofing > AutoCorrect Options... and set it to replace \lambda by λ. The replacement string is a touch too long but at least that makes it the same as the typesetting Formula Editor that one would use mainly in Word. While I was about it I also set _0, _1, _2 etc. to give subscripts. As someone who never uses direct cell references, I found it irritating that X1, along with 17 billion other possible defined names had been squandered on such a ridiculous notation (Don't worry, I do not expect you to agree with that assessment!). At least I can now use {x₁, x₂, x₃, x₄, x₅} with impunity.
@MyOnlineTrainingHub
Жыл бұрын
Great to hear, Celia!
@MyOnlineTrainingHub
Жыл бұрын
Love that idea, Peter. I'm going to borrow it 😉
@williamthatsmyname
Жыл бұрын
Absolutely brilliant. Thanks again for the tutorial.
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it 😊
@alvarorodriguezlasso
Жыл бұрын
Fantastic: regards from Cali-Colombia
@MyOnlineTrainingHub
Жыл бұрын
Thanks so much!
@jimfitch
Жыл бұрын
An awesome start! Thanks for the tutorial & for making us aware.
@MyOnlineTrainingHub
Жыл бұрын
Cheers, Jim!
@jimfitch
Жыл бұрын
@@MyOnlineTrainingHub Cheers, Mynda! Merry Christmas (or is it Happy Christmas in Australia?) to you, Phil, & family!
@MyOnlineTrainingHub
Жыл бұрын
Thanks so much, Jim! And to you and yours 🎅
@mattschoular8844
Жыл бұрын
This looks great. This may prove helpful to some when writing formulas. However, the user will need the basic understanding of what they are trying to achieve in the AFE. I suppose that is why it is suitably named Advanced Formula Environment. Still a bit of a chicken and egg situation though. Thanks for sharing, Mynda. Happy Holidays
@MyOnlineTrainingHub
Жыл бұрын
I agree, Matt. I was originally created to help with authoring LAMBDAs, and it has been expanded to have more general usability.
@chrism9037
Жыл бұрын
This is really cool, thanks Mynda!
@MyOnlineTrainingHub
Жыл бұрын
Cheers, Chris 🙏
@emilionunziata1775
Жыл бұрын
can you please tell me if it work even in other language and system localizations?
@Fredick.7
Жыл бұрын
Linda persona, eres genial. Gracias por compartir este muy interesante material.
@MyOnlineTrainingHub
Жыл бұрын
My pleasure 😊
@quentinbricard
Жыл бұрын
Thank you for this video, it's very interesting!
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it 😊
@nocturneuh
5 ай бұрын
Thank you.
@MyOnlineTrainingHub
5 ай бұрын
You're welcome!
@rainerbotte
Жыл бұрын
Love it...youre the greatest!
@MyOnlineTrainingHub
Жыл бұрын
Thanks so much 🙏
@michaelenriquez577
Жыл бұрын
Have you had any issues with power query on the last 2 days? I’m having issues with it wherein it takes ages to manage queries
@MyOnlineTrainingHub
Жыл бұрын
No, but I haven't been using it that much in the last two days, sorry.
@erikguzik8204
Жыл бұрын
I know i had this add in, in my excel workbooks, and I think it moved off the home tab, but now I cannot find it anywhere. have looked for it but the add in appears to be missing and nothing to load in again? do you know if its going thru an upgrade to be released again sometime soon, or what?
@MyOnlineTrainingHub
Жыл бұрын
It's now called Excel Labs and should be on the home tab.
@erikguzik8204
Жыл бұрын
@@MyOnlineTrainingHub got it back now. looked up Excel Labs and reinstalled the add-in. Thanks much. I didn't notice it was gone cause i done use it much. Thanks so much. Now i can edit my LET() functions much easier.
@cedjulemckeever
Жыл бұрын
Is this still available? I cannot seem to find it. There is a formula editor, but it does not look like the one in your video. I am using MS365 Family version.
@MyOnlineTrainingHub
Жыл бұрын
It is, but it's called Excel Labs now 🙄
@cedjulemckeever
Жыл бұрын
@@MyOnlineTrainingHub Thank You, again.
@chrismoule7242
Жыл бұрын
0:42 - mine appears on the Formula tab, fyi
@MyOnlineTrainingHub
Жыл бұрын
Awesome! Must be an update since I recorded the video.
@peterbartholomew7409
Жыл бұрын
@@MyOnlineTrainingHub The formula tab is a much more appropriate place for the AFE! Jack Williams (Microsoft Research) picked up on a recommendation I made (along with others) that the user should move the AFE to the Formula Ribbon tab and stated that the default location had been changed. I assume that would require a fresh install.
@geoffkendall2006
Жыл бұрын
Not much of this was working for me until I saw that Lambda functions are only available to Office 365 subscribers and that it might well be necessary to update my installed version of Excel. Fortunately, I have a 365 subscription so could update and - hey presto! - things started working. I was seeing #NAME? errors before, presumably because I was looking for a (lambda) function that could not actually exist on my machine, even though I had been through the process of making one as per Mynda's fine tutorial.
@MyOnlineTrainingHub
Жыл бұрын
Glad you got it working 👍
@andreminis3976
Жыл бұрын
Hi Mynda , Great review. I have tried to unwrap the formula in the formula bar as you showed in your video. When I click the check mark nothing happens. Is there another way to solve this?
@MyOnlineTrainingHub
Жыл бұрын
You can copy the formula out of the AFE and paste it in the formula bar.
@windyrainStorm
5 ай бұрын
What's the difference of this from Excel Lab? I could not find this AFE but I do have Excel labs.
@MyOnlineTrainingHub
5 ай бұрын
AFE uses a GPT model to write the formulas. Labs is Microsoft AI.
@iankr
Жыл бұрын
Many thanks, Mynda! How do you enter the lambda Greek letter symbol? Do you use the ASCII code with the Alt key?
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it, Ian! I used the Insert > Symbol tool to add the Lambda sign to a cell, which I then copied out. If you scroll through the comments you'll see a reply to CeliaAlvesSolveExcel's question that has some other useful suggestions.
@iankr
Жыл бұрын
@@MyOnlineTrainingHub Many thanks, Mynda.
@troelsnielsen2848
Жыл бұрын
By december 2022 they should have added localization support directly in AFE
@MyOnlineTrainingHub
Жыл бұрын
mmm, I can't speak to that.
@IvanCortinas_ES
Жыл бұрын
Does it only recognize the "comma" separator within the function syntax?
@MyOnlineTrainingHub
Жыл бұрын
I talk about localisation at the end of the video. AFE has some support for localisation of formulas and no support for localisation of app text. Formulas in the AFE must be edited using a comma argument separator, for example =SUM(A2,C2,E2), however the AFE will interact with workbooks using other separators such as semi-colon (;). When reading or saving a formula, the AFE will automatically translate between formats. Function names can be written using the workbook’s locale, but you can force English function names via the settings (see screenshot below). The AFE will eventually support full formula localisation, rather than requiring comma argument separators.
@jackwilliams2895
Жыл бұрын
AFE does now work with "semi-colon" separator. We made this change after the recording.
@IvanCortinas_ES
Жыл бұрын
@@jackwilliams2895 Thank you Jack. Now it's a complete tool!
@sunnybaggu785
Жыл бұрын
Hey Mynda, Iam not getting the Wrapped formula in AFE while typing formulas in Grid formula Editor. Is it because of Web version. (2.23 time of the video).
@MyOnlineTrainingHub
Жыл бұрын
It could be because the AFE pane is too wide. Make it narrower to see if that helps.
@jackwilliams2895
Жыл бұрын
Mynda is right. We wrap the formula only when it does not fit within the width of the editor. Resizing the window and using Ctrl-Shift-F or Right-click -> format will update the layout according to the width of the window.
@sunnybaggu785
Жыл бұрын
@@MyOnlineTrainingHub thank you Mynda...let me try it and update you...thank you for the reply.
@sunnybaggu785
Жыл бұрын
@@jackwilliams2895 thank you for sharing this
@sunnybaggu785
Жыл бұрын
iam unable to get it , may be its 365 web version
@advrohitowhal9794
Жыл бұрын
Great !
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it 😊
@IamTheReaper911
Жыл бұрын
💯💯
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it!
@sentralorigin
Жыл бұрын
my job doesn't allow access to the Microsoft Store 😞
@MyOnlineTrainingHub
Жыл бұрын
Bummer 😏
@eulogiotimoteoperezramos3872
Жыл бұрын
Please verify: NetVAT = amt/(1+VATRate)
@MyOnlineTrainingHub
Жыл бұрын
Correct.
@rob4433
Жыл бұрын
Hi!! Please help me! I have been a long-time sub to your channel - learnt a lot, thank you!! However, I want to refresh my learning on forecasting (you produced a video, I can't recall the title, New AI forecasting or something). But it showed how you could set up the chart really well. Do you have a link you can share with me please?
@MyOnlineTrainingHub
Жыл бұрын
I think you mean this one: kzitem.info/news/bejne/mrB8t6aIi6mhn2U
@rob4433
Жыл бұрын
@@MyOnlineTrainingHub thank you so much, that's exactly the one I wanted. Have a great holiday!!
Пікірлер: 94