LAMBDA: The ultimate Excel worksheet function

Excel formulas are written by an order of magnitude more users than all the C, C++, C#, Java, and Python programmers in the world combined.

Excel is not usually considered as a programming language due to two fundamental weaknesses:

a. The Excel formula language really only supported scalar values — numbers, strings, and Booleans.
b. It didn’t let users define new functions. Sure UDFs could be created , but it required knowledge of VBA.

LAMBDA allows users to define new functions in Excel’s own language.
It has made Excel turing-complete. In principle, this means that any computation can be done in the Excel formula language.

It’s introduction is a qualitative shift, not merely an incremental change.
Read Microsoft’s official research blog here.

Let us look at an example. Excel does not have an inbuilt function to calculate the Square of a number.

First go to Formulas > Name Manager and create a New Name.

Give a name to the Function and write the Formula.

Use it in a cell.

Let us try another formula.
This will count weekends between two dates, assuming a 5-day working week.

=LAMBDA(start_date,end_date,(end_date-start_date+1) — NETWORKDAYS.INTL(start_date,end_date,1))