The LAMBDA function is my new favorite thing in Excel, after templates. It allows you to create your own custom named functions without VBA! You can then use those custom functions throughout your workbook and easily copy them to other workbooks.
In Google Sheets, the feature is called "Named Functions" and it is equally as powerful, though the "Advanced Formula Environment" in the Excel Labs add-in makes it a lot easier to write the functions in Excel. See Named Functions in Google Sheets.
If you are already familiar with "Named Ranges" in Excel or Google Sheets, then "Named Functions" are going to be easy for you to work with. You'll find the Named Functions within the Name Manager in Excel, or via Data > Named Functions in Google Sheets.
A Simple Example =LINSPACE(xstart,xend,n)
One of the things I do all the time is create sequences of numbers from x_start to x_end. If you want numbers 1:50, that's easy to do with the built-in SEQUENCE function: =SEQUENCE(50). However, how do you create a graph with 51 points between -250 and 100? Easy: =LINSPACE(-250,100,51). Done.
If you want to use LINSPACE in another workbook, find a cell that uses the function and then just copy and paste that cell into your other workbook. The named function is copied along with it.
You might be asking "Where do I find documentation and examples for how to use the LINSPACE function?" Good lead-in question ...
Introducing the LAMBDA Library
I have launched a new project on Vertex42.com called the LAMBDA Library: a collection of fully documented custom functions that you can use within your own workbooks and projects. So far, the library has over 50 new functions.
You can view the entire list on the Functions page. Here are a few based on category:
- Sequence/Grids: LINSPACE, MESHGRID, RESCALE, SE
- Interpolation: LINTERP, PINTERP, CSPLINE
- Solvers: QUADRATIC, SOLVE_NR, POLYROOTS
- Arrays: COMBINATIONS, CIRCSHIFT, FLIP, SPLICE, REPARRAY
- Matrices: DIAG, DOT, CROSS, HESS, QR, EIGENVALUE
- Polynomials: POLYFIT, POLYVAL, POLYDER, POLYMULT, PPVAL
- Complex Numbers: IM_CTRANSPOSE, IM_DOT, IM_MMULT, IM_POLYROOTS
- Text Manipulation: ARRAY2TEXT, COUNTCHAR
If you are familiar with the Python library NumPy, or Matlab, or Javascript, some of the names of these functions may look familiar. That is intentional because it's easier to jump back and forth between different software and languages if the function names are similar (assuming the functions do similar things).
Functions You Can Use Today
The easiest way to see working examples of the functions is to download the LAMBDA Library Template. It contains a list of all the functions, plus working examples that you can play with.
Here is a screenshot of the examples from the template for LINSPACE, LOGSPACE and MESHGRID:
But We Can Already Do That with VBA
It is true that there are already many add-ins that provide numerous functions for engineering, math, and statistics. If you are happily using one of these add-ins, cool. But, if you are running into roadblocks due to VBA, LAMBDA functions may provide a new solution.
Sharing: How portable are the spreadsheets that rely on add-ins? Can they be shared with anyone without the add-in? LAMBDA functions do not rely on add-ins, but people will need Microsoft 365 (older versions of Excel will not work with LAMBDA functions).
Security: Are VBA functions limited in your company due to security issues? Do the VBA functions work in shared spreadsheets online? Do VBA-based functions work when opening the file on your phone? LAMBDA functions are saved in regular .xlsx files and do not use VBA.
Optimization: How easy is it to use Goal Seek or Solver when your spreadsheet model relies on VBA? LAMBDA functions work fine with Goal Seek, Solver, and Data Tables.
What are Some Powerful Examples of LAMBDA?
With AI, Python integration, and LAMBDA functions, I think the next few years is going to be a very exciting period for spreadsheet development, especially for engineers and scientists.
I don't want you to go away thinking "Whoop de Do - I can make a function for the area of a rectangle. Check, please."
Here are 3 examples of LAMBDA functions that are much more than the area of a rectangle.
1. INTERPOLATION
It doesn't need to be complex to be powerful. A simple tool that you use frequently can be just as powerful as something very complicated that you only use once. Linear interpolation using the L_LINTERP function is an example of a simple tool that can be very useful for modeling.
=L_LINTERP(x,known_xs,known_ys)
If you think linear interpolation is too simple to demonstrate the power of a LAMBDA function, check out PINTERP for polynomial interpolation or CSPLINE for cubic spline interpolation.
2. NEWTON-RAPHSON SOLVER
Have you ever used Goal Seek? Even though it may be a simplistic solver compared to the Solver add-in, it is still very powerful. It is an implementation of the Newton-Raphson method which may be one of the most important and influential algorithms of all time.
The SOLVE_NR function is an implementation of the Newton-Raphson method as a LAMBDA function (basically Goal Seek for functions).
3. EIGENVALUES
So far, I think the most technically challenging LAMBDA function I've seen or made is the EIGENVALUE function. This function performs QR decomposition iteratively to find the eigenvalues of a square matrix. It can also be used to find the roots of a polynomial. I'll be working on extending this function to work with complex numbers.
=L_EIGENVALUE(matrix,iterations)
That's Cool. How do I Create My Own Functions?
So, you've tried a few examples, seen the power of the LAMBDA function, and now you want to create your own functions? Awesome!
I've written up a detailed guide, including everything I've learned so far about techniques for creating functions, using optional parameters, simulating for loops, and recursion. See "Create Lambda Functions in Excel"
Want to Contribute?
If you have requests for functions, or want to share functions that you have made, please comment below. One of the ways to share LAMBDA functions is using a GitHub Gist. You can just share the link to your Gist. For example, here is the GitHub Gist for the LAMBDA Library:
https://gist.github.com/jonwittwer/13e1c25374ef9de7d708e43db9e0f442
Also, if you have questions about any of the functions in the LAMBDA Library, or want to report errors or suggestions for improvement, please share using the comments below. You can also contact me directly via email.
[Any comment that looks like spam will be deleted, but I read all comments that make it past the spam filter]
Comments