LAMBDA: Advanced Formula Environment
Description
Microsoft's Advanced Formula Environment is designed to make entering and editing large named formulas, such as are common for LAMBDAs, easier.
Introduced in early 2022, it is still in its infancy but provides a key feature that I feel is crucial to what I see as the future of Excel modeling: Importing pre-built, pre-tested functions from trusted repositories.
Pre-Built, Pre-Tested Functions
Microsoft provides a large library of pre-built, pre-tested functions in Excel. With this library of functions we can assemble projects far quicker and with much greater reliability than if we relied solely on basic algebra in each cell. And now, with LAMBDA we can create our own library of functions, test them, and use them in all our projects just like Excel's built in library.
Trusted Repositories
Once we create our pre-built, pre-tested functions we can store them in places where we can share them with others. There are many options including network drives, One Drive, Google Drive, Dropbox, etc. There is a special repository used by developers called GitHub. GitHub is a free repository for storing source files. LAMBDAs are source files and so GitHub is a good choice to hold our library of functions. GitHub is also integrated into Advanced Formula Environment.
I store LAMBDAs that I share here: https://gist.github.com/CHatmaker. You are free to use them, but you really want the Advanced Formula Environment installed on your PC to make loading them into your projects easy.
Getting Advanced Formula Environment Add-in
The add-in is free and available directly from within Excel's developer tab. If you don't have the developer tab, click the Insert tab > Get Add-ins. In the search box enter Advanced Formula Environment. When it appears in the list click Add.
Importing LAMBDAs from GitHub Gists
With the Advanced Formula Environment added to our Excel, click the Home tab > Advanced Formula Environment. Next, click the download icon. It asks for a URL. To test, use this URL:
https://gist.github.com/CHatmaker/3e1708888ec2bd1cde2ec9d002dc459b
That is my library for working with arrays of amounts posted to dates.
Once the library has been downloaded, it is not in Name Manager (where all named LAMBDAs go) yet. To get it into Named Manager we must click the Sync Names with Excel's Named Manager icon. At this point, LAMBDAs are ready for use.
All of my LAMBDAs can be found here: https://gist.github.com/CHatmaker.
To see more Gists, click here: https://gist.github.com/search?p=2&q=%22Excel+LAMBDA%22
