The LAMBDA function in Excel is a powerful tool that allows you to create custom, reusable functions without needing VBA (Visual Basic for Applications) or macros. With LAMBDA, you can define a formula with parameters and call it like any other Excel function. This makes complex calculations more manageable and enhances reusability across your workbooks.
Syntax
| Formula | Description |
|---|---|
| =LAMBDA([parameter1, parameter2, …], calculation) | Creates a custom function with defined parameters that can be reused in your workbook. |
- parameter1, parameter2, ...: These are the inputs for your LAMBDA function. They are optional, and you can define as many parameters as needed.
- calculation: The formula or calculation that uses the parameters. This is the logic of your custom function.
Example
Suppose you frequently need to calculate the total cost, including tax, for a series of purchases. Instead of writing the formula repeatedly, you can define a LAMBDA function that takes the price and tax rate as inputs and returns the total cost.
| Input | Value |
|---|---|
| Price per Unit | $30 |
| Number of Units | 15 |
| Tax Rate | 8% |
To create a LAMBDA function that calculates the total cost, including tax, use the following formula:
=LAMBDA(price, units, taxRate, price * units * (1 + taxRate))You can then call this LAMBDA function with specific values, like this:
=LAMBDA(price, units, taxRate, price * units * (1 + taxRate))(30, 15, 0.08)This will return the total cost for the purchase, including the specified tax rate.
Practice Exercise 1
Task: Create a LAMBDA function to calculate the area of a rectangle, given the length and width. Then, calculate the area for a rectangle with a length of 20 units and a width of 10 units.
Hint: Define a LAMBDA function that multiplies the length by the width to get the area.
Practice Exercise 2
Task: Create a LAMBDA function to calculate the compound interest for an investment. The function should take the principal amount, annual interest rate, and number of years as inputs. Calculate the compound interest for a principal of $2,000, an annual interest rate of 5%, and a period of 4 years.
Hint: Use the formula for compound interest, which is P * (1 + r)^n - P, and define it as a LAMBDA function with the appropriate parameters.
Solution for Exercise 1:
=LAMBDA(length, width, length * width)(20, 10)Explanation: This LAMBDA function calculates the area of the rectangle by multiplying the length and width. For a length of 20 units and a width of 10 units, the area is 200 square units.
Solution for Exercise 2:
=LAMBDA(P, r, n, P * (1 + r)^n - P)(2000, 0.05, 4)Explanation: This LAMBDA function calculates the compound interest on an investment. For a principal of $2,000, an annual interest rate of 5%, and a period of 4 years, the compound interest earned is calculated using the formula provided.