Tag Archives: let

LET’s LAMBDA!

So Microsoft is rolling out LET and LAMBDA. Using The Wisdom of the Ancients (also known as the Excel SDK) something similar can be done using good, old fashioned, xlfEvaluate. The function XLL.EVAL takes any Excel formula as its first argument and evaluates it using the remaining arguments. It uses _1, _2, ... in the formula to refer to the arguments. If you want named arguments use key-value pairs like {foo, 1.23}. These can be mixed and matched at your pleasure.

Their Thanksgiving example would be XLL.EVAL(Thanksgiving, {year, 2020}) where Thanksgiving is the string "TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), """mm/dd/yyyy""")", although "DATE(year,11,MOD(12-WEEKDAY(DATE(year,11,1)),7)+22)" seems simpler.

One thing to keep in mind is that references and defined names occurring in the first argument must be preceded with an exclamation point. Don’t ask me why because I don’t know. Excel is Excel.