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, 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 because the functions are saved in a module named "L" using the Excel Labs add-in.

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.

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.

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.

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.

Step 1: Install and Open the "Excel Labs" Add-In from Microsoft (aka "Advanced Formula Environment")

1. Go to Home > Add-Ins
2. Search for "Excel Labs, a Microsoft Garage project" which used to be "Advanced Formula Environment"
3. Open the Excel Labs add-in (opens in a side pane)
4. 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.

https://gist.github.com/jonwittwer/13e1c25374ef9de7d708e43db9e0f442

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]

Licensing

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.