The term “eta lambda” is short for “eta reduced lambda”. Although the name sounds complicated, the intent is actually the reverse. Eta lambda refers to a simpler syntax for passing in calculations to newer functions like BYROW, BYCOL, SCAN, REDUCE, etc., which are designed to accept a lambda expression.
The concept of eta reduction
In lambda calculus, “eta” refers to the simplification of a function (more technically, the “eta-reduction of a function”), when it behaves identically for all inputs. In Excel, eta reduction is related to simplifying functions defined by LAMBA. The idea is to allow Excel to use an abbreviated syntax when possible. For example, In the formula below, a LAMBDA passes x into the SUM function and returns the result, which is the same as the SUM function alone:
LAMBDA(x,SUM(x))=SUM(x)
Since the above relationship is true, Excel allows this formula:
=BYROW(array,LAMBDA(x,SUM(x)))
To be abbreviated (eta reduced) like this:
=BYROW(array,SUM)
Note that eta reduction only applies to newer functions like BYROW, BYCOL, REDUCE, SCAN, etc., that accept custom LAMBDA functions. Let’s look at a specific example.
Eta lambda example
In the worksheet above, the goal is to calculate an average for each person in the list based on the four quiz scores as shown. Before dynamic array formulas were introduced to Excel, the problem would be solved with a formula like this in cell H5:
=AVERAGE(C5:F5)
After the formula was entered, it would be copied down to row 16 to calculate an average for all 12 students. Once dynamic arrays were introduced, it was possible to use the BYROW function to do the same thing in a single formula like this:
=BYROW(C5:F16,LAMBDA(x,AVERAGE(x)))
This formula can be entered into cell H5 and will spill into the range H5:H16. Notice that to call the AVERAGE function, we need to package it into a custom LAMBDA function like this:
=LAMBDA(x,AVERAGE(x))
Here, the “x” in the function is just a placeholder for the values in each row in C5:C16. The BYROW function iterates through each row in the range and calls the custom LAMBDA on each row, which invokes AVERAGE. This works fine, but the syntax is somewhat verbose for such a simple operation. With the introduction of eta lambdas, you can dispense with packaging the AVERAGE function in a custom LAMBDA and invoke the function by name only like this:
=BYROW(C5:F16,AVERAGE)
The Excel formula engine knows how to pass each row into the AVERAGE function, and the final result is exactly the same as before. You can use the same syntax to call other functions like this:
=BYROW(C5:F16,SUM) // sum each row
=BYROW(C5:F16,MAX) // max of each row
=BYROW(C5:F16,MIN) // min of each row
=BYROW(C5:F16,COUNT) // count of each row
Note: The eta lambda syntax works best with functions that can accept a single argument, such as SUM, MIN, MAX, COUNT, COUNTA, etc.
Excel 365 refers to the subscription version of Excel as opposed to a fixed version of like Excel 2019, Excel 2016, Excel 2013, etc.
Typically, the subscription version of Excel is available as part of a suite of applications called “Office 365”, which was renamed “Microsoft 365” in 2020. You can see subscription status by navigating to File > Account, which shows current product information. The subscription version of Excel is still downloaded and launched like a normal application on Windows and Mac OS.
The key thing to understand is that Excel 365 has new features that are not yet available in any other version of Excel. A good example is Dynamic Array Formulas , which include new functions like FILTER , UNIQUE , SORT , SORTBY , SEQUENCE , RANDARRAY , XMATCH , and XLOOKUP .
Note: Excel 365 should not be confused with “Excel Online” or “Office Online”. These are limited feature applications that run in a browser.
