![]() Once named, you call the function by name, eliminating the need to repeat entire formulas when you want to use them. If we name our function PYTHAGORAS, then a formula such as PYTHAGORAS(3,4) evaluates to 5. The existing Name Manager in Excel allows any formula to be given a name. ![]() The function takes two arguments named X and Y, binds the value of X*X to the name XS, binds the value of Y*Y to YS, and returns SQRT( XS+YS) as its result. LAMBDA complements the March 2020 release of LET, which allows us to structure the same example like this: =LAMBDA( X, Y, LET( XS, X*X, YS, Y*Y, SQRT( XS+YS ) ) ) To illustrate the power of LAMBDA, here’s a function written using the notation to compute the length of the hypotenuse of a right-angled triangle: =LAMBDA( X, Y, SQRT( X*X+Y*Y ) ) Its incorporation into Excel represents a qualitative shift, not just an incremental change. Researchers have known since the 1960s that Church’s lambda notation is a foundation for a wide range of programming languages and hence is a highly expressive programming construct in its own right. We discussed LAMBDA and some of our research on spreadsheets in a sponsored video presented at POPL 2021. The initial release has some implementation restrictions that we expect to lift in the future. LAMBDA is available to members of the Insiders: Beta program. ![]() You can now, in principle, write any computation in the Excel formula language. With LAMBDA, Excel has become Turing-complete. These newly defined functions can call other LAMBDA-defined functions, to arbitrary depth, even recursively. In December 2020, we announced LAMBDA, which allows users to define new functions written in Excel’s own formula language, directly addressing our second challenge. These changes are a substantial start on our first challenge: rich, fully-first-class structured data in Excel. At the 2019 ACM SIGPLAN Symposium on Principles of Programming Languages (POPL 2019), we announced two significant developments (opens in new tab): data types (opens in new tab) take Excel beyond text and numbers and allow cells to contain first-class records, including entities linked to external data, and dynamic arrays (opens in new tab) allow ordinary formulas to compute whole arrays that spill into adjacent cells. The fruits of that partnership are starting to appear in the product itself. The Calc Intelligence (opens in new tab) project at Microsoft Research Cambridge has a long-standing partnership with the Excel team to transform spreadsheet formulas into a full-fledged programming language. Over the years, two particular shortcomings have stood out: (1) the Excel formula language really only supported scalar values-numbers, strings, and Booleans-and (2) it didn’t let users define new functions. Despite its success, considered as a programming language Excel has fundamental weaknesses. 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. It’s also the world’s most widely used programming language. Ever since it was released in the 1980s, Microsoft Excel has changed how people organize, analyze, and visualize their data, providing a basis for decision-making for the millions of people who use it each day.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |