Create your own custom Excel functions with LAMBDA

The cost per unit calculation is a very easy formula to create and copy to other cells. However, we are using a very simple formula in this example so as not to distract from learning LAMBDA. The real power of LAMBDA function will be realized by using it to recreate formulas.

Now let’s follow our listed steps above.

1. Test the formula: In cell D2, I enter =B2/C2. Since it is a table, the unit cost is filled in on each row and the formula works.

2. Create it LAMBDA in the worksheet: in cell F2 (you can use any cell outside your table), I create the LAMBDA. I enter =LAMBDA(Total cost,Quantity,Total cost/Quantity)(B2,C2). Total cost is the first parameter, Quantity is the second parameter, and Total cost/quantity is the calculation. We then set the first parameter as B2 (for this calculation just to make sure it works) and set the second parameter as C2. the LAMBDA works.

3. Set the LAMBDA in the name manager: Copy only the LAMBDA without the cell references for that specific formula to create a custom function that can be used repeatedly, =LAMBDA (Total Cost, Quantity, Total Cost/Quantity). Go to the Formulas tab and choose Set name of Defined names group. Paste copy LAMBDA in the Refers to: box. Enter the name you want to give to your new custom function in the field name: box. I named mine CostPerUnit. If you want to add comments, you can add them in the Comment: box. This is especially useful if your settings might need more explanation.

See the screenshot below for the New name window for Cost per unit.