The LAMBDA Library
The LAMBDA function in Excel and the Named Function feature in Google Sheets allow you to define your own named functions that can be reused throughout your spreadsheet, without VBA!
The LAMBDA Library hosted by Vertex42.com is a collection of fully documented custom functions that you can use individually or as a whole, for free. Our goal is to make new functions readily available, to aid in education, and improve the functionality of the spreadsheet as a modeling tool. This is a work-in-progress and many functions need your help in testing and reporting issues. Thank you!
Learn How to Use LAMBDAs
If you have a custom function defined in your spreadsheet, you can use it just like any other function. Excel even shows you the syntax for the function in a tooltip, like the example below for the custom linear interpolation function, L_LINTERP.
You can name a function whatever you want, but to keep the functions distinct from built-in spreadsheet functions, it is common to use a prefix for your functions. The LAMBDA Library uses "L_" as a prefix for all custom functions.
For additional basic information about the LAMBDA function, see the following article:
The LAMBDA Function in Excel - at support.microsoft.com
Learn How to Create LAMBDAs
You can create a named function in Excel using the Name Manager, like the simplistic example below which calculates the area of a rectangle from a given length and width:
The power of the LAMBDA function comes from being able to define much more complicated formulas. It is practically impossible to do that from scratch within the tiny Refers To field in the Name Manager. Instead, you create and test the LAMBDA function using the formula bar, or another editor, and then you copy and paste the formula into the Refers To field.
Get the Excel Labs Add-in
See Create LAMBDA Functions for an in-depth guide.
How to Get the LAMBDA Library Functions into Excel
Method 1: Download a Template Containing the Functions
This is the simplest method. Our templates contain working examples of the functions.Get the Template!
Method 2: Copy/Paste a Specific Function
Download the above template, which contains a worksheet with a list of all the functions. Getting a single function into another file is as simple as copy/pasting a cell containing the function.
When you copy/paste a cell that contains the function, the function is automatically added to the Name Manager. Any other custom function that is used within that LAMBDA will also be copied.
When you copy a worksheet from one workbook into another, ALL of the Named Ranges, Named Functions, and other Names with Workbook Scope (as well as those defined with your current worksheet scope) are copied into the other workbook.
So BE AWARE that this happens. Named Ranges have always worked this way, but if you have a file containing 100s of custom named functions, you need to be aware that all of these will be copied along with the worksheet.
Method 3: Import Functions via a Gist using Excel Labs
This is a great way to get ALL the functions into an existing file, but make sure that you WANT all of the new Named Functions within your file before you use this technique.
If you import these functions into your existing file, the functions might conflict with an existing name, and you would be importing all of the functions at once.
Before importing from a Gist, always experiment with a blank new Excel file first. Then, check the Name Manager to see what functions and names were added.
If you only need one or a few functions from this library, use Method 2.
Step 1: Install and Open the "Excel Labs" Add-In from Microsoft (aka "Advanced Formula Environment")
- Go to Home > Add-Ins
- Search for "Excel Labs, a Microsoft Garage project" which used to be "Advanced Formula Environment"
- Open the Excel Labs add-in (opens in a side pane)
- Go to Modules and click on the Cloud icon to import from URL
Step 2: Import the following Gist URL
You can skim through the code in the gist to see what is included.
Step 3: Click on the Save icon in the Excel Labs panel. This will save the Lambdas as Named Functions in your workbook.
Step 4: View the Lambdas via Formulas > Name Manager, or press F3
Method 4: Add Functions One at a Time via Name Manager
The LAMBDA function code is provided in the documention for you to copy/paste. Although it would be good to have the experience of creating a function from scratch, Method 2 is a lot easier.
Due to the redundancy of the Name Manager code vs. the Excel Labs AFE code, this library may sometimes only provide the AFE version of the code. To paste manually into the Name Manager, you can copy just the =LAMBDA(...) portion of the code, leaving off the ";" at the end.
Accuracy and Precision
The functions in the LAMBDA Library need to be tested. We are still in the experimental phase of the project. In some cases, the algorithms may not represent the best method or even the most widely used method, and may result in significant inaccuracies and precision errors.
It is already known that functions performing arithmetic with complex numbers may have errors significantly larger than the typical floating point errors of other real functions because the complex numbers in Excel are stored as text with at most 15 significant digits of precision. This is evident when comparing =2/3+1/3 vs. =COMPLEX(2/3,0)+COMPLEX(1/3,0).
See the article about Floating-point arithmetic at learn.microsoft.com.
The primary goal of the LAMBDA Library is to aid in the development of more advanced mathematical algorithms for Excel. So, the order of priority of the objectives is this:
- Is a function/algorithm feasible?: Before considering accuracy and precision, is it even feasible to make an algorithm work as a LAMBDA function? Can the needed iteration, recursion, and calculations be done at all? [This is where we are currently at with most functions in the LAMBDA Library.]
- Accuracy and robustness: If the algorithm is working for a few test cases, is it also robust to user errors such as invalid input types? Does it produce the expected results within most practical use cases? Are the known limitations documented? [Getting closer with many functions]
- Optimized precision and efficiency: Is the function using the best-known methods with regard to precision and computational efficiency? [Not yet our priority]
- Conforming to IEEE software standards: Do the functions hold up to various scientific standards? [Perhaps some day]
See our License page for information about how you may use these functions in your own files and projects.
Help Contribute to the Library
For now, contributions to the library have been made primarily by Jon Wittwer of Vertex42.com. If you wish to participate in the effort, provide feedback, comments, examples, etc. then please contact Dr. Wittwer.