Creating Custom Excel Functions with LAMBDA

Image by Editor

What is the LAMBDA Function?

LAMBDA function creates reusable custom-named functions. It is introduced in the Excel 2021 version. It takes arguments as parameters and calculation takes the usual formula to return the calculated result, just like built-in Excel functions. The LAMBDA function can replace complex formulas with a single function.

Syntax:

=LAMBDA(parameter_or_calculation,...)

• parameter: Input arguments for your custom function, it can be a string or number. You can enter up to 253 parameters.
• calculation: The usual Excel formula or logic to calculate the result. It must be the last argument. This argument is required.

After creating a custom function, you can name that function for reuse in Excel.

Creating Custom Functions Using LAMBDA

Consider you have a small online business; you want to calculate the sales with tax. As you need to calculate the sales tax frequently instead of using a general formula you can create a reusable custom function using LAMBDA.

Step 1: Create the LAMBDA Function

Suppose you apply a 10% tax on product price. Let’s create the LAMBDA function to calculate the total price after applying tax.

Formula:

=LAMBDA(price,price*1.1)

• price: The sales amount is the input parameter.
• price* 1.1: It’s the calculation that calculates the total price after applying 10% tax on the price.

Step 2: Use the LAMBDA Function

Let’s directly use the LAMBDA function in Excel.

• Select cell F2 and insert the following formula.

Formula:

=LAMBDA(price,price*1.1)(100)

This formula returns 110, which is 100 plus 10% tax.

In the Excel updates version, you can use the following formula to calculate all product prices.

Formula:

=LAMBDA(price,price*1.1)(E2:E13)

This formula will return the Total Price with Tax for the selected range.

Step 3: Name the Custom Function

• Go to the Formulas tab >> select Define Name.
• Set Name as Price_with_Tax.
• Insert the following formula in the Refers to box and click OK.

=LAMBDA(price,price*1.1)

Step 4: Use the Custom Function

Once you create the custom function, you can use it like a regular function.

• Select cell G2 and insert the following formula.

Formula:

=Price_with_Tax(E2)

This function returns 110 (10% of 100 added to 100).

Practical Examples of LAMBDA Functions

1. Calculate the Area in Excel

For construction purposes, you may need to calculate the area in square feet of various rooms or properties. You can create geometric custom functions to calculate the area of a rectangle. Insert the formula of rectangle area which multiplies length with width.

Formula:

=LAMBDA(length, width, length * width)

This LAMBDA function takes length and width as inputs and returns the product.

To make the function reusable, you can name the function Area_Square_Feet.

• Select a cell and insert the following formula.

=Area_Square_Feet(B2,C2)

This formula will return 300 by multiplying 20 by 15.

2. Calculate Final Price After Discount + Tax

You can create a custom function that applies tax and discount and then calculates the final price, by using LET and LAMBDA functions.

Formula:

=LAMBDA(price, discountRate, taxRate,
LET(
discountedPrice, price * (1 - discountRate),
finalPrice, discountedPrice * (1 + taxRate),
finalPrice
)
)

• LAMBDA: It akes price, discount rate, and tax as input variables
• LET: Defines the following variables:
  • discountedPrice: Calculates the price after applying the discount.
  • finalPrice: Applies tax to the discounted price.
• Return Value: Returns the calculated finalPrice as the output.

Name the function as CalculateFinalPrice using Name Manager.

• Select a cell H2 and insert the following formula.

Formula:

=CalculateFinalPrice(100, 0.05, 0.1)

This formula will return the final price after applying a 5% discount and 10% Tax.

Common Errors and Troubleshooting

• #CALC! Error: The LAMBDA function shows this error if the function is not called correctly with proper inputs. Ensure you test it using input values (arguments) at the end.
• #NAME? Error: If you use an incorrect function name in Excel it may show this error. Make sure you’ve named it correctly in the Name Manager.

Download Practice Workbook

Conclusion

You can create reusable custom functions in Excel using LAMBDA. The LAMBDA function is a game-changer for Excel users who want to create custom functions without VBA. By following the step-by-step guide, you can create any type of custom function. Test the formula before using it in LAMDA’s calculation. Explore the LAMBDA function with basic to advanced custom functions.