Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn about the newly released Advanced Formula Editor which is available as an Add-in the Excel Desktop, Mac and Online platforms. Let’s get started
Video
Advanced Formula Environment
The Advanced Formula Environment allows users to create, modify, reuse and import formulas from URL and text. The AFE was released on the 8th of February 2022 by the Excel Team and you can reach more here
Insert AFE Add-in
The first thing we’d want to do is to fetch the add from the Office Add-in store. To do that
- In the Add-ins group of the Insert tab, click on Get Add-ins
- In the Office Add-ins window, type in Advanced Formula Editor in the search box
- Click Enter
- Click on Add and and Continue respectively
The newly added add-in will be visible in the far right of the Home tab as seen below
Sample Data
For our operation, we have sample dataset formatted as an Excel Table below
Non-contiguous Filter
Based on the sample data, we want to filter the dataset such that we will have 3 columns returned – Brand, Region and Total. In addition, we want to filter the Total that is greater than or equal to 1,000,000 and Year that equals to 2014. To do this, we are going to use the dynamic array Filter function twice and also use the array constant which will give us the columns we want to returned when we set it to 1 (true).
- In cell J3, execute the formula: =FILTER(salesData[[Brand]:[Total]],(salesData[Year]=2014)*(salesData[Total]>=1000000))
- Click Enter.
From the screenshot below, we have all the columns from Brand to Total returned with Total greater than or equals to 1000000 and Year that equals to 2014.
However, as stated earlier, we do not all the columns from Brand to Total; we only want Brand, Region and Total columns which are not next to each other. Therefore, we are going to use the array constant. To factor in the array constant:
- In cell J3, execute F2 to put the formula in the edit mode
- Modify the formula as follows: =FILTER(FILTER(salesData[[Brand]:[Total]],(salesData[Year]=2014)*(salesData[Total]>=1000000)),{1,0,1,0,0,1})
- Click Enter
In the above modified formula, we used another FILTER Function which takes the first FILTER formula as the input value for the second filter function array argument. Then, we introduced curly brackets which has 1 for columns we want to keep and 0 for columns we do not want in the final result. From the caption below, we can see that we only have the 3 columns we want to return
Introduction of LAMBDA Function
Next, we want to pack the formula inside our LAMBDA Function which we can reuse. To read more about the LAMBDA Function, click here
- In cell J3 again, execute F2 to put the formula in the edit mode
- Modify the formula as thus: =LAMBDA(mySelection,myYear,myTotal,FILTER(FILTER(mySelection,(myYear=2014)*(myTotal>=1000000)),{1,0,1,0,0,1}))
- Click Enter
In the above formula, we defined three parameters as follows: mySelection, myYear and myTotal respectively. Note, you can use whatever name or letters for your parameter. Then, we passed in the parameters into the appropriate structured reference Tables syntax.
For example, we replaced salesData[[Brand]:[Total]] with mySelection; salesData[Year] with myYear and salesData[Total] with myTotal respectively
When we clicked Enter, we are certain to get the Calc! error. Next, we need to copy the formula in cell J3 excluding the equal sign
- In the Manager tab of the Advanced Formula Editor pane, click on + icon
- In the Formula name, type in myFunction
- In the Refers to box, paste the formula using CTL + V
- Click on Add. From the screenshot below, we can see that the LAMBDA Function in added to the AFE
We can edit, rename, delete and even share
Next, we want to synchronize the formula with the name manager.
- Click on sync located directly below the Manager tab
Custom Function Verification in the Named Manager
We want to verify whether our custom function in available in the named manager as the synchronization
- In the Defined Named group of the Formulas tab, click on Name Manager
From the caption below, we can see the myFunction custom function we created!!
Use Custom Function
Finally, we want to use the custom function to return the non-contiguous filter with Total greater than or equal to 1000000 and Year that equals to 2014
- Clear whatever we have in cell J3 and then execute the custom function: =myFunction(salesData[[Brand]:[Total]],salesData[Year],salesData[Total])
- Click Enter
Voila, we have the accurate result as seen in the caption below
See you in the next tutorial