Recursive Lambda Functions
They say ‘when all you have is a hammer, everything looks like a nail’. At the beginning of my career, all I had was Excel 2007, and I swung that thing with reckless abandon. You’d be amazed (or horrified) at the complex things you can build with Excel.
When I started programming full-time, I had to leave the spreadsheet world behind. However, the recent addition of Lambda Functions to Google Sheets got my attention. I’ve always wanted to write recursive spreadsheeet functions without Javascript/VBA, and I started wondering whether this addition would make that possible. I couldn’t help my curiosity, so this is my investigation into creating recursive formulas within Google Sheets.
(just kidding, just kidding)
The Test
Our goal is to create an ‘Integer -> Roman Numeral’ converter from scratch. For example, we’d need to turn ‘6’ into ‘VI’.
I’ll show a couple of the traditional ways to do it, and use the new Lambda Functions at the end.
Javascript/VBA approach
This is the “correct” way to create our converter, but it’s like taking a helicopter to the top of Mount Everest. It works, but for this challenge, we want the purity of doing it with formulas.
Traditional Formulas
The below is a basic way of trying to implement recursion in a spreadsheet. The formula is dragged down, so that each cell in column ‘D’ and ‘E’ references the column above it. Each row is the equivalent of a ‘step’ or ‘loop’ in a programming language. Then we just concatenate all of the values in the column.
This method returned the correct answer for our input, but it’s a very messy way to format things and it would return an error if we input a number so large that there weren’t enough rows in Google Sheets to perform the calculation.
Lambda Functions (This is the new method)
To create a recursive formula in Google Sheets, you need to create a ‘named function’ and combine it with a ‘lambda function’.
For example, in the below, I created a ‘named function’ called FACT_LAMBDA and placed it within a lambda function.
I’m telling the Lambda to run the ‘Fact Lambda’ function multiple times, decreasing the ‘n’ variable each time, until ‘n’ is equal to 1. Once n is equal to 1, no further calculations are performed.
(The below is just recreating the factorial function)
Then we can run our ‘named function’ like it was any other function.
So, can we create a recursive function without the use of VBA/Javascript? Yes!
Using Lambda For Our Converter
For the problem we actually want to solve (converting integers to roman numerals), I ran into a roadblock.
To create our converter, we need to keep track of two variables, the ‘running total’ and the ‘roman numeral’ string.
(reminder of how we did it in previous methods)
The issue is that the recursive functions can only return a single result (like what would populate in a cell), and I need to pass two results to the recursive function each time. In a programming language, I’d use an array to store both results in a single entity, but I don’t think there is a similar data structure in Google Sheets.
I resolved this by creating my own ‘array’. Using string manipulation and setting ‘|’ as the delimiter, I can make it so that every ‘|’ indicates a new variable.
So, in the below, Google Sheets only recognizes 1 variable on each line (which is the max I can pass to the recursive function), but then I can split that one variable into three distinct variables that I can use in my formula.
Here’s a visualization of that process.
If it helps, here’s the formula.
Now that we have the concept down, we can finally implement our solution.
The name of our function will be ‘ROMAN_CONV’, and we’ll place that in a Lambda function.
Our formula will repeatedly retrieve the next Roman Numeral until our running total equals zero.
Let’s try out our ‘ROMAN_CONV’ function.
It works!
Conclusion
Can you combine ‘Named’ and ‘Lambda’ functions to create recursive functions? Yes!
Should you? Probably not!
But isn’t it cool that we can?
----------------
Here is the link to the Google Sheets Worksheet: Sheet Link
If you know of a cooler way to do it, let me know! LAMERS @ OUTLOOK . COM
----------------
If you’re interested in being notified of new posts, feel free to sign up using the below form.