What is the excel LAMBDA function?

Until recently, user-defined functions were the prerogative of programmers. The introduction of LAMBDA in Excel has made them available for everyone. The new LAMBDA function brings functional programming to Excel. Using LAMBDA, users will be able to write custom functions in Excel, native to the workbook.  Simply put, you can now take any existing formula, however complex it is, wrap it up in LAMBDA, and give it any name you like. Then, instead of typing your original lengthy formula, you can refer to that name anywhere in your workbook.

For example, if a function of the form ‘MAX(ABS([Range]))<Rounding’ is used throughout a workbook, a user could define a custom LAMBDA function called MAXABS which would return the same result.  LAMBDAs are defined in the name manager.

How can it be useful?

What makes the LAMBDA function powerful is that you can have LAMBDAs calling other LAMBDAs or even recur them.  Effectively, LAMBDA makes Excel a functional programming language. Theoretically, you can now build any computable program in Excel (i.e. LAMBDA makes Excel Turing complete, a long-time goal for Microsoft).

Microsoft recently announced that Excel would soon be letting users define and use custom datatypes, another hallmark of programming. Excel continues to port programming features over to Excel, many of which are likely to be designed to directly support and complement LAMBDAs.

What is the downside?

On paper, LAMBDA sounds like it could save the financial modelling and auditing industry a lot of time, factoring out repetitive work and replacing complicated formulas with simple LAMBDA functions.  However, there is very little support for reviewing and debugging LAMBDA formulas at this stage. Thus, any audit of a workbook using extensive LAMBDA functions is likely to be very expensive.

Another deterrent to implementing LAMBDAs in the industry is that they are not backward compatible. The current industry standard is to maintain backward compatibility with Excel 2013; thus, we would not expect to see any serious movements on this for several years.  That said, as Microsoft continues to integrate programming features into its Excel suite, new features supporting LAMBDAs might enable/accelerate their uptake in financial modelling. LAMBDA is now available in the office insider beta channel and will be soon come to the main Excel program.

• Read more: Scripting: Automating your workload