Writing Excel formulas with the help of Name Manager can be a demanding job. When you write simple formulas, you will still somehow manage. With complex formulas or changing existing ones, there is a high chance that you will make a mistake. The LAMBDA feature allows you to create your own functions as names added in the Name Manager, and to make this job easier, Microsoft has created an Excel add-in for creating complex formulas: Advanced Formula Environment.
You can install this add-on by first going to the Insert ribbon and then clicking Get Add-ins. After this action, the Office Add-ins window will open. Then type “Advanced Formula” in the search box. You will soon see an application called Advanced Formula Environment. By clicking on Add you add it to Excel and after installation it will appear as the last icon in the Home ribbon.
If you click on the Advanced Formula Environment icon, a panel will open on the right in which we add formulas. Here you will see a pop-up window that offers the option to Take a Tour or Try Examples.
For now we will skip this step. Let’s create a simple table in the worksheet with two columns: RADIUS and AREA. In the first we enter the values of the radius, and in the second we will calculate the area of the circle. Then we need to define the function. In the advanced editor, select the Manager tab, click on “+” and then enter the name of the “Area” function. The area of a circle is calculated as the product of the number Pi and the square of the radius. Let’s write this formula using the LAMBDA function:
Area = LAMBDA (r, PI () * r ^ 2)
After we added the formula, the name used to call the function has not yet been defined. In order to do that, we need to synchronize the editor with the Name Manager by clicking on the appropriate button. After that, we will define the name Area, by calling which we refer to the LAMBDA function for calculating the area of the circle. We will easily use the name later in the table to calculate the area.
The formulas we create in the advanced editor are easy to change, rename, delete and share. When sharing the editor will generate text that you can copy to the clipboard.
By clicking on the Editor tab, we can add new formulas more easily. For each of them, it is necessary to write the name first, then the sign of equality, and then the expression that makes up the formula. A semicolon should be added at the end of each formula.
Advanced Formula Environment will certainly make it easier for you to write long formulas, but keep in mind that this is an add-in, so it has some bugs. First of all, it will not work in every version of Excel. This bug is probably due to the inability of the add-on to launch Internet browser so that it can display the content in the panel on the right. Next, take care of the regional settings. For now, only US settings work, when you use a comma to separate arguments. If you use a semicolon for this purpose, the add-in will not work. If the development team removes the bugs mentioned above and probably some others, we will get a great tool for creating LAMBDA formulas.