Operis Analysis Kit
  • Applications
    • Spreadsheet Modelling
    • Spreadsheet Review
  • Features
  • Pricing
  • Support
    • FAQ
    • Documentation
  • Blog
  • About OAK
  • Get in touch
Operis Analysis Kit
  • Applications
    • Spreadsheet Modelling
    • Spreadsheet Review
  • Features
  • Pricing
  • Support
    • FAQ
    • Documentation
  • Blog
  • About OAK
  • Get in touch

Start your free trial

Blog

Excel LAMBDA function 9 Feb 2022

Share now

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

Share now

Categories

  • Discover OAK
  • Modelling Insights
  • OAK Releases

Archive

Everything you need to build, analyze and audit a spreadsheet. And make better decisions

Try OAK for free
OAK
  • Features
  • Pricing
  • About OAK
  • Support
Legal
  • Privacy Notice
  • Cookie Policy
  • Legal disclaimers
OAK Applications
  • Spreadsheet Modelling
  • Spreadsheet Review
Contact us
  • 110 Cannon Street
    London
    EC4N 6EU
  • +44 (0)20 7562 0400
  • [javascript protected email address]
Winners of the 2020 Financial Modelling Innovation Awards

©2023 Operis Analysis Kit. All Rights Reserved.

Powered by FL1 Digital