Excel’s LAMBDA Function and Turing Completeness
Despite the rise of programming languages such as R and Python in actuarial circles, Microsoft Excel remains a key tool in many actuaries’ arsenals. In part, Excel’s continued widespread use is a result of an almost universal familiarity across the industry of Excel workbooks and their in-built functionality.
The complex formulas present in many actuarial spreadsheets, often copied and pasted multiple times across different worksheets, are a common cause of spreadsheet error and make it difficult for auditors or other users of the spreadsheet to establish the purpose of the function. Microsoft’s recent announcement of the new LAMBDA function in Excel, which allows the user to create their own reusable Excel functions, goes some way towards addressing these concerns.
Background and a simple example
Until now, Excel has only allowed users to create custom functions through another coding language such as VBA or JavaScript. There are two main limitations with this approach. Firstly, it required the user to learn the required syntax to code in another language, and secondly some organisations limit the use of macros or scripts in Excel workbooks for security reasons.
The LAMBDA function avoids these two limitations by allowing users to create custom functions directly within an Excel workbook using the standard Excel formula language. To investigate the power of this new function, here is a simple example.
Consider a table of exam results for a group of students who all sat two papers. Grades are awarded according the following system:
- Any student who receives a mark below 40 on either paper receives a FAIL grade
- Any student who achieves a mark of at least 70 on both papers receives a DISTINCTION
- All other students receive a PASS
The following formula could be typed into cell C2 to output the grade for the first student
=IF(OR(A2<40,B2<40),”FAIL”,IF(AND(A2>=70,B2>=70),”DISTINCTION”,”PASS”))
Good coding practice would assign the grade boundaries of 40 and 70 to a named range in the worksheet, however we can ignore that for this simple example. In order to calculate the grade for all students, this formula can be copied down the cells in column C.
Although this formula provides the required answer, there are a couple of limitations with this approach. Firstly, another user of the spreadsheet may be unclear about what this formula is calculating. Secondly, if the underlying logic of the formula needs to be changed (for example the grading rule was changed so that a distinction grade was instead awarded when the average mark was above 70), then this change would need to be replicated everywhere the formula appears within the workbook. This can be a significant issue if the formula is repeated multiple times across multiple sheets as it can be both time consuming to rectify and there is a likelihood of missing out some occurrences.
The LAMBDA function
The LAMBDA function provides an alternative approach to this problem. A custom function called EXAMGRADE can be created within Excel’s Name Manager (which is available in the Formulas tab). The required function can be defined as a LAMBDA function in the “Refers to:” box giving sensible names to the input parameters.
=LAMBDA(exam1,exam2,IF(OR(exam1<40,exam2<40),”FAIL”,IF(AND(exam1>=70,exam2>=70),”DISTINCTION”,”PASS”)))
The required syntax for a LAMBDA function consists of a list of required parameters, in this case the two exam grades, followed by an expression that calculates the required output. Name Manager allows the user to choose the scope of their custom function. Each custom function can either apply throughout the workbook or just an individual sheet. Once saved in Name Manager, using EXAMGRADE will now call the user defined function.
Entering =EXAMGRADE(A2,B2) into cell C2 will now output the exam grade for the first student and this formula can be copied down column C as before. Adopting the LAMBDA function approach is both clearer for other users of the spreadsheet and helps prevent formula errors. The name of the function gives other users a better idea of what the function is aiming to do and they can still access the underlying logic by accessing Name Manager. Furthermore, if any changes are required to be made to the function, then changing the definition of the function within Name Manager will alter all occurrences of the EXAMGRADE function within the spreadsheet.
Recursion
In addition to allowing users to define their own functions, the LAMBDA function also allows the creation of recursive functions. This opens up the possibility of creating functions in Excel that perform tasks that were previously only possible through the use of a script. Recursive functions can be created by calling the function within the definition of the function itself. For example, define INTSUM(n) in Name Manager as
=LAMBDA(n,IF(n=1,1,n+INTSUM(n-1)))
This function will sum all the integers from 1 up to n, so INTSUM(10) would give 55, the sum of all integers from 1 to 10. To see how this works in practice, note that INTSUM(1) will return the value 1. The function then applies recursively to all other integers by adding the integer n to the sum of all integers from 1 to n-1. So INTSUM(10) is defined as 10+INTSUM(9), INTSUM(9) is defined as 9+INTSUM(8), and so on down to INTSUM(2)=2+INTSUM(1) and INTSUM(1)=1.
Typing INTSUM(10) into Excel will carry out this recursive calculation within the cell to output the correct sum. INTSUM(10)=10+9+8+7+6+5+4+3+2+1=55
Another example of recursion using Excel’s LAMBDA function is to compute the nth term of the Fibonacci sequence. The Fibonacci sequence is defined recursively so that the next element of the sequence is the sum of the previous two. Setting the first term and second term equal to 1 gives the standard Fibonacci sequence:
1, 1, 2, 3, 5, 8, 13, 21, 34, 55, …
In Excel’s Name Manager, define FIBONACCI(a,b,n) as
=LAMBDA(a,b,n,IF(n=1,a,If(n=2,b,FIBONACCI(b,a+b,n-1)))
This function will output the nth term in the Fibonacci sequence with first term a and second term b. So FIBONACCI(1,1,9) would output 34, the 9th element in the standard Fibonacci sequence above.
This function uses the fact that the nth term in the Fibonacci sequence starting a,b is the (n-1)th term in the Fibonacci sequence starting b, a+b. So 5, the 5th element of the Fibonacci sequence starting 1,1 is:
- The 4th element of the Fibonacci sequence starting 1,2
- The 3rd element of the Fibonacci sequence starting 2,3
- The 2nd element of the Fibonacci sequence starting 3,5
For n greater than 2, the definition of the function above will return FIBONACCI(a,b,n)=FIBONACCI(b,a+b,n-1). Using this result, FIBONACCI(1,1,5) can be built up recursively. As n in this case is greater than 2, the IF statement in the definition of FIBONACCI(1,1,5) will call FIBONACCI(1,2,4). This is using the fact above that the 5th element of the Fibonacci sequence starting 1,1 is the same as the 4th element of the Fibonacci sequence starting 1,2. As n is still greater than 2, FIBONACCI(1,2,4) will call the function FIBONACCI(2,3,3) which in turn will call FIBONACCI(3,5,2). As n is now equal to 2, FIBONACCI(3,5,2) gives the output value 5. Hence FIBONACCI(1,1,5) will output the value 5 through recursion.
While the examples given here are relatively simple, the introduction of recursion in Excel will allow the creation of complex iterative functions which will apply repeatedly before producing an output. Such iterative functions would previously have required the creation of a loop within VBA or another scripting language. With the addition of iterative functions to Excel, the Excel formula language joins other programming languages such as Java, Python and R as being Turing complete.
Turing completeness
Turing completeness is a term used to describe any programming language that is able to replicate a Turing machine. Turing machines are a theoretical type of computer that perform operations on an infinitely long piece of tape which consists of 0s and 1s. Turing machines are able to perform four basic functions:
- Make a mark at the current location on the tape (i.e. change a 0 to a 1)
- Remove a mark at the current location on the tape (i.e. change a 1 to a 0)
- Move one place to the left
- Move one place to the right
Alan Turing’s work in the 1930’s showed that given a finite set of rules (or states) a Turing machine is able to compute the result of any equation that it is possible to compute. Each state sets out a series of actions for the machine to perform. For example, state 1 could give the following instructions:
- If the current location on the tape has a 0 change this to a 1, move one place to the right and change to state 3
- If the current location has a 1 on the tape then do not change the mark, move one place to the right and change to state 5
The recursive nature of Excel’s LAMBDA function allows a replica of a Turing machine to be built within the Excel formula language. A LAMBDA function can be defined that takes as its inputs the initial state of the tape, the initial position along the tape and the initial state of the programme. The function can be defined using a series of IF statements to carry out the required actions for any given state (e.g. rewriting a 1 to a 0, moving one place to the right, and changing the state of the programme). The function will output the new state of the tape, the new position along the tape, and the new state the programme is in.
The LAMBDA function can then be called again, within the definition of the function, using these outputs as new values for the input variables. The recursive nature of this function will continue this process until reaching a stop state where the function will output the value of the tape.
What’s next?
Currently, the LAMBDA function is still in development, available to beta testers of Microsoft 365. As such, there are still some areas which could be improved. Although Name Manager provides a convenient place to store custom built functions, Name Manager was not designed with this purpose in mind and there could be some improvements to the way custom functions are defined and a way of separating LAMBDA functions from other named ranges.
Additionally, when typing a formula into Excel’s formula bar, Excel’s Intellisense help box appears to autocomplete function names and shows the required arguments for built in functions. Currently, Intellisense will autocomplete the name of a LAMBDA function, as it does for other named ranges, but does not provide the user with a list of required arguments for the function. This might not pose too much of an issue for the designer of the LAMBDA function, however other users wishing to make use of the function may benefit from this. Furthermore, there is no current way of easily sharing custom functions between different workbooks.
However, putting these minor issues aside, LAMBDA functions significantly increase the power of the Excel formula language, they allow the user to perform calculations in Excel that would previously have required running an external script in VBA or Java. While other programming languages such as R or Python may have certain advantages over Excel, Excel is more widely used and understood within the industry and so the LAMBDA function is likely to gain traction within the actuarial community as this new feature is rolled out more widely.
Jack Foley
April 2021
