Table of Contents
The LAMBDA function in Excel allows users to create custom functions without the need for complex formulas or VBA coding. It allows for the creation of personalized functions by defining parameters and specifying the desired output. This function can be used in various scenarios, such as automating repetitive tasks, simplifying complex calculations, and improving the efficiency of data analysis. By using the LAMBDA function, users can improve their workflow and save time and effort in Excel.
Use a LAMBDA function to create custom, reusable functions and call them by a friendly name. The new function is available throughout the workbook and called like native Excel functions.
You can create a function for a commonly used formula, eliminate the need to copy and paste this formula (which can be error-prone), and effectively add your own functions to the native Excel function library. Furthermore, a LAMBDA function doesn’t require VBA, macros or JavaScript, so non-programmers can also benefit from its use.
Syntax
=LAMBDA([parameter1, parameter2, …,] calculation)
Argument | Description |
|---|---|
parameter | A value that you want to pass to the function, such as a cell reference, string or number. You can enter up to 253 parameters. This argument is optional. |
calculation | The formula you want to execute and return as the result of the function. It must be the last argument and it must return a result. This argument is required. |
Remarks
Lambda names and parameters follow the Excel syntax rules for names, with one exception: don’t use a period (.) in a parameter name. For more information, see Names in formulas.
Make sure you follow best practices when creating a LAMBDA function as you do with any native Excel formula, such as passing of the correct number and type of arguments, matching open and close parentheses, and entering numbers as unformatted . Also, when you use the Evaluate command, Excel immediately returns the result of the LAMBDA function and you can’t step into it. For more information, see Detect errors in formulas.
Errors
If you enter more than 253 parameters, Excel returns a #VALUE! error.
If an incorrect number of arguments is passed to a LAMBDA function, Excel returns a #VALUE! error.
If you call a LAMBDA function from within itself and the call is circular, Excel can return a #NUM! error if there are too many recursive calls.
If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.
Examples
Define the following in the Name Manager:
Name: | ToCelsius |
Scope: | Workbook |
Comment: | Convert a Fahrenheit temperature to Celsius |
Refers to: | =LAMBDA(temp, (5/9) * (Temp-32)) |
Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Data | |
104 | |
86 | |
68 | |
50 | |
32 | |
Formula | Result |
=TOCELSIUS(A2) | 40 |
=TOCELSIUS(A3) | 30 |
=TOCELSIUS(A4) | 20 |
=TOCELSIUS(A5) | 10 |
=TOCELSIUS(A6) | 0 |
Define the following in the Name Manager:
Name: | Hypotenuse |
Scope: | Workbook |
Comment: | Returns the length of the hypotenuse of a right triangle |
Refers to: | =LAMBDA(a, b, SQRT((a^2+b^2))) |
Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Data | |
3 | 4 |
5 | 12 |
7 | 24 |
9 | 40 |
Formula | Result |
=HYPOTENUSE(A2,B2) | 5 |
=HYPOTENUSE(A3,B3) | 13 |
=HYPOTENUSE(A4,B4) | 25 |
=HYPOTENUSE(A5,B5) | 41 |
Define the following in the Name Manager:
Name: | CountWords |
Scope: | Workbook |
Comment: | Returns the word count in a text string |
Refers to: | =LAMBDA(text, LEN(TRIM(text)) – LEN(SUBSTITUTE(TRIM(text), ” “, “”)) + 1) |
Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
Data | |
Something wicked this way comes. | |
I came, I saw, I conquered. | |
A quick brown fox jumped over the lazy dog. | |
Use the Force, Luke! | |
Formula | Result |
=COUNTWORDS(A2) | 5 |
=COUNTWORDS(A3) | 6 |
=COUNTWORDS(A4) | 9 |
=COUNTWORDS(A5) | 4 |
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
See Also
Cite this article
stats writer (2024). How can I use the LAMBDA function in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-lambda-function-in-excel/
stats writer. "How can I use the LAMBDA function in Excel?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-lambda-function-in-excel/.
stats writer. "How can I use the LAMBDA function in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-lambda-function-in-excel/.
stats writer (2024) 'How can I use the LAMBDA function in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-lambda-function-in-excel/.
[1] stats writer, "How can I use the LAMBDA function in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the LAMBDA function in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.