Maximizing Communication in Excel 365 with Dynamic Arrays, Lambda Functions, and ChatGPT

As a pricing actuary, I must build illustrations for other actuaries, IT illustration programmers, and admin systems. The name of the game is to get downstream customers and stakeholders to understand how new products work. The faster users can understand the logic and calculations; the quicker developers can bring all the supporting systems for the latest product online, ultimately allowing products to go to market much earlier.

Classic Excel can hide logic in plain sight with all the complicated formulas and cell references. You can interpret this logic, but it is time-consuming and highly tedious—especially if it is overly complex—because cell reference contains no information about the problem. Cell references are just a location pointer to the data. To learn the calculation, the user must follow the maze of pointers and assemble the picture of the calculations. For large, complicated spreadsheets, this can be a nightmare. But at the same time, having all the details of calculations displayed in front of the users is fantastic. How can we get the incredible display without the cell-reference hell? The key is harnessing Excel 365's new Let and Lambda functions and the dynamic arrays.

LET

A Let function allows you to define variables within a cell. For example, a classical difficult-to-read Excel formula might be:

= -$A2 + SQRT($A2*$A2 – 4*$F7*$G9)/(2*$F7).

The same formula with a Let function would be:

=LET(a,$F7,b,$A2,c,$G9,

-b + SQRT(b*b – 4*a*c)/(2*a)).

You can now name the cell references a, b, and c within a cell with a Let function. You can then use the variables in a formula and divorce the logic from the reference, making the problem context more explicit.

LAMBDA

A Lambda function allows the user to define a reusable function in Excel without Visual Basic for Application (VBA). You can define it inline within a Let, define it in a cell on its own, or name it in the Name Manager. For example, declare the Lambda within the Name Manager and name the equation above the QuadraticEquation. You would define it as:

=LAMBDA(a, b, c, -b + SQRT(b*b – 4*a*c)/(2*a)).

Then, within a cell, you call it by:

=QuadraticEquation($F7, $A2, $G9).

Again, this example is great because you have separated your logic from cell references, but when I read the cell, I must figure out what those cells refer to. I improve it like this:

=LET(a,$F7,b,$A2,c,$G9, QuadraticEquation(a, b, c)).

The cell tells me exactly what the cells represent and how they feed the equation. All vital information is right before my eyes, dramatically reducing the cognitive load in understanding what is happening. In classic Excel, I must read the equation. Then, go to the referenced cells, which may refer to others. Eventually, I would piece together the puzzle and return to the original problem. Can you see how much easier this is?

Advanced Formula Environment

We now have a new problem. With Lambda defined in the Name Manager, I cannot see the inner of the equation to help understand what is going on. Luckily, Excel has—somewhat—solved this problem. Excel introduced the Advanced Formula Environment (AFE), which contains helpful tools for dealing with Lambda functions, such as a formula debugger. (I say somewhat because the debugger currently doesn't work with dynamic arrays and lambda helper functions, which is hugely disappointing.) Once you have added the AFE, you will get the box below.

The red arrow points to how you turn on and off the debugger. The green arrow points to the Lambda function defined in the Name Manager. The blue error points to the debugger information, 1000X better than the evaluate formula functionality that comes in standard in Excel.  

Dynamic Arrays

Dynamic Arrays in Excel 365 let you work with a range of values in a single cell. Instead of entering a formula that outputs to a single cell, you can use a formula that "spills" results into neighboring cells automatically. For example, a formula like =SEQUENCE(3,2) will create a 3-row by 2-column array of numbers starting from 1. The results will "spill" into the cells below and to the right of where you entered the formula.

There are significant concepts to consider:

1. Spilling: The formula automatically fills cells with multiple values.
2. Dynamic: The output adjusts if the source data or formula changes.
3. Functions: Lambda functions can be applied consistently over the entire array.

Dynamic arrays make handling and analyzing large datasets easier without manually adjusting ranges or using complex formulas. The # at the end of the cell reference, like A12#, specifies that it is a dynamic array.

Why Did Excel Introduce Lambda and Dynamic Arrays?

Before you can understand the motivation for this new functionality, we need to understand computer science basics of programming paradigms. There are three major programming paradigms: structural, object-oriented, and functional.

1. A structural language is programming by giving the computer a task list and order of operations. As the program executes, it transforms the data to get the result. This paradigm is the most straightforward and most familiar. It is in such languages as VBA, C, or Fortran. I would also put the feel of classical Excel into this bucket too.
2. Object-oriented languages, such as C++, C#, or Ada, are programming by delegating responsibilities to classes. The developer will create classes where they give data and responsibilities through functions. Each class communicates by passing messages to each other to accomplish a goal, just as a manager coordinates with many teammates to achieve a task.
3. Functional languages, such as Haskell, define abstractions and their relationships to each other. The programmer defines functions that change or morph the data to its desired result. Functional programmers write software by composing pure functions, avoiding mutable states and side effects. The central concepts are:
   1. Pure functions: Functions that return the same output for the same input and have no side effects, generally not true for procedural or object-oriented languages.
   2. Higher-order functions: Functions that take other functions as arguments or return functions.
   3. Immutability: Corresponds to unchangeable data structures after their creation.
   4. Function composition: Combining functions to create more complex functions.
   5. Recursion: Due to immutability, there is no concept of a “for” or “while loop” like in procedural languages. To iterate over a structure requires recursion, which is a function that calls itself.

The Lambda functions and dynamic arrays turn Excel from a procedural language to a functional one—a game changer! The functional paradigm will require you to think about problems differently, but it will immensely improve your communication of complex calculations with Excel. Purity makes testing and validation of Excel calculations much easier because you do not have to worry about other parts of the workbook impacting the calculations.

Lambdas make Excel feel more like a traditional functional programming language, with the advantage of seeing all the intermediate steps. The best of both worlds! The Let functions and dynamic arrays cover the immutability. The Lambda functions enforce purity, higher-order operations, composition, and recursion. Let's get a better feel for what this change feels like.

Let's Look at a Live Example

Let's look at classical Excel below. This example is the withdrawal penalty formula of the AIG Polaris Max prospectus coded in Excel. Notice there is no context for the different cell references, which means scrolling over the entire workbook to find the applicable information.

Let's look at this same equation using the lambda helper function, Map, which takes a series of arrays and transforms them into a result. The first step in changing the spreadsheet to functional is to replace the classical formula with the Map in the following table. The Map separates the cell references from the logic, a massive step in the correct direction! (I have lined up the cell references with their function parameter representation, making it easy to see how the cell reference corresponds to the variables. For instance, $D8 as prevCV, $P8 as PrevMAWA, and etc.) However, we are not using dynamic arrays because there is no # at the end of the cell references. This example is nothing more than beneficial syntactic sugar preparation for going fully functional.

Now, let's go fully functional by putting all the domain-specific functions into a lambda library. The reason for not stopping with the first step by just converting to maps is that the interdependency among the different abstractions is still spread throughout the workbook, hiding them in plain sight. The Lambda Libraries (pictured below) make the dependencies in the workbook extremely specific.

Inevitably, actuarial illustrations moving to total functional programming forces all interdependent calculations into a single dynamic array, which is a self-contained set. All self-contained sets are independent variables that feed other dynamic arrays.

Now For the Cool Stuff!

After translating the Excel workbook into purely functional lambda libraries, versioning the libraries in GitHub Gists is a snap. Excel's AFE can consume a link from GitHub to download the Lambda Libraries into Excel. Essentially, version controlling your critical business logic, which up to now took expensive third-party software like Incisive.

Our time-consuming battle is to get other interested parties to understand the logic. Now, rather than passing around god-awful spreadsheets that no one can read, organizations can create version-controlled Lambda Libraries that are consumable by other departments. New spreadsheets illustrating results could be plug-and-play of actuarial calculations with thoroughly tested, consistent, company-approved logic. The actuaries only need to modify the abstractions that truly change, which also helps communicate differences. The future is bright!

But it gets even better! We must also get admin systems and illustration people to understand the logic. ChatGPT can convert the Lambda Libraries to another programming language, which takes seconds to minutes. There were six steps to get this up and running.

1. I converted the Excel Lambda Library to C# using ChatGPT, which could be any language.
2. I created a C#, Windows, Command Line Visual Studio project.
3. I put the ChatGPT conversion result into a class called PolarisMax.
4. I threw the input into a data structure, called inputs, that I looped over in the C# code below.
5. I declared the initial values above the loop and set the new values at the bottom because the next step needs the prior step's information, which will happen when using a Scan-like function in Excel. These are the prevCV, prevIB, prevICB, and prevMawa declared variables.
6. Each column set in Excel from left to right becomes a function call from top to bottom within the C# for each loop. The C# function calls have the same name as the Lambda Library, which makes understanding the connection easy.

In this little example alone, converting from classic Excel to a working program would have taken four or five hours to pull apart and get working. This process took me 10 minutes to convert the code and bang out a quick test reproducing my spreadsheet results! No matter who is doing the project, this is a simple, repeatable pattern and process.

The Bottom Line

The challenge of getting products to market is getting all the different systems and processes to understand the new products. Classic Excel was good because the calculations were transparent, but the logic was challenging to follow. The new Lambda functions and dynamic libraries take Excel to a new level. They make Excel feel much more like a functional programming language, which illustrates all its calculations. The best of both worlds! If you are willing to embrace the functional programming paradigm, then Excel becomes a communication device—like no other. But ultimately, it will require you to relearn how to attack problems. Learning different programming paradigms is a helpful exercise for learning to solve problems better. Only good things can come from this exercise.

You can easily version control the logic, which was impossible in the past. GitHub encourages massive code collaboration across distributed people. Everyone can understand when, why, and how the logic changed. Workbooks can easily download Lambda Libraries from GitHub. The libraries can be tested and approved for use around the organization due to the advantages of purity and immutability.

Statements of fact and opinions expressed herein are those of the individual authors and are not necessarily those of the Society of Actuaries, the newsletter editors, or the respective authors’ employers.