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.
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.
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.
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.
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 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. :)
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!
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
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?
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.
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.
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 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.
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.
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).
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.
@@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.
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.
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?
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.
So great to hear 😊
Such a great demonstration of Advanced Fx editor! Wow!
Thank you!
Excellent video! Thanks for sharing. There is so much potential using AFE.
Totally agree!
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.
They sure are 😊 Glad you enjoyed it!
Extremely useful, Mynda! Thank you for this. Question: how do you type in the lambda character?
Type 039B (for upper Lamba) or 039b (for lower Lambda), then [Alt] X. Or you can import Lambda symbol (Insert > Symbol)
@@roseventura1711 thank you :)
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.
Great to hear, Celia!
Love that idea, Peter. I'm going to borrow it 😉
Fantastic: regards from Cali-Colombia
Thanks so much!
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.
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.
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.
Glad you're making use of it. I expect it will eventually be a built-in feature.
@@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. :)
Absolutely brilliant. Thanks again for the tutorial.
Glad you liked it 😊
Hi Mynda!Great Tutorial.I Have Used Advanced Enviroment A Few Times And Found it Really Impressive...Thank You :)
Great to hear, Darryl 🙏
This is really cool, thanks Mynda!
Cheers, Chris 🙏
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!
Glad it was helpful! Happy holidays to you too 😊
An awesome start! Thanks for the tutorial & for making us aware.
Cheers, Jim!
@@MyOnlineTrainingHub Cheers, Mynda! Merry Christmas (or is it Happy Christmas in Australia?) to you, Phil, & family!
Thanks so much, Jim! And to you and yours 🎅
Thank you for this content. Your explanation is very clear. Very few videos AFE is available in RUclips.
Thanks so much 😊
Wow these are great improvements. Once they add in things like auto parenthesis insertion and mouse selection, it be just about perfect
Yep 😊
Linda persona, eres genial. Gracias por compartir este muy interesante material.
My pleasure 😊
Thank you for this video, it's very interesting!
Glad you liked it 😊
can you please tell me if it work even in other language and system localizations?
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
I agree, Matt. I was originally created to help with authoring LAMBDAs, and it has been expanded to have more general usability.
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?
You can copy the formula out of the AFE and paste it in the formula bar.
Love it...youre the greatest!
Thanks so much 🙏
Many thanks, Mynda! How do you enter the lambda Greek letter symbol? Do you use the ASCII code with the Alt key?
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.
@@MyOnlineTrainingHub Many thanks, Mynda.
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
No, but I haven't been using it that much in the last two days, sorry.
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.
It is, but it's called Excel Labs now 🙄
@@MyOnlineTrainingHub Thank You, again.
What's the difference of this from Excel Lab? I could not find this AFE but I do have Excel labs.
AFE uses a GPT model to write the formulas. Labs is Microsoft AI.
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?
It's now called Excel Labs and should be on the home tab.
@@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.
Thank you.
You're welcome!
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.
Glad you got it working 👍
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).
It could be because the AFE pane is too wide. Make it narrower to see if that helps.
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.
@@MyOnlineTrainingHub thank you Mynda...let me try it and update you...thank you for the reply.
@@jackwilliams2895 thank you for sharing this
iam unable to get it , may be its 365 web version
0:42 - mine appears on the Formula tab, fyi
Awesome! Must be an update since I recorded the video.
@@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.
Does it only recognize the "comma" separator within the function syntax?
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.
AFE does now work with "semi-colon" separator. We made this change after the recording.
@@jackwilliams2895 Thank you Jack. Now it's a complete tool!
By december 2022 they should have added localization support directly in AFE
mmm, I can't speak to that.
Great !
Glad you liked it 😊
my job doesn't allow access to the Microsoft Store 😞
Bummer 😏
💯💯
Glad you liked it!
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?
I think you mean this one: ruclips.net/video/9zFQrRRwpl0/видео.html
@@MyOnlineTrainingHub thank you so much, that's exactly the one I wanted. Have a great holiday!!
Thank you! And you 😊
Please verify: NetVAT = amt/(1+VATRate)
Correct.