Summary of my custom LAMBDAs for Large Numbers

After the new LAMBDA function was released in Excel early December 2020 and playing around a little bit with that function and its recursive features, I quickly asked myself: would it be possible to perform calculations with large numbers in Excel by only using formulas?

Now, some weeks later, I can say: yes, that’s possible! I started with some array and text functions as helper functions and then implemented LNUM.SUM for adding large numbers in Excel. After that, I created the function LNUM.SUBTRACT to be able to subtract two large numbers. And I finally implemented LNUM.PRODUCT for multiplying two larger numbers, which then uses LNUM.SUM and LNUM.SUBTRACT.

I wrote an article about each function in this blog, where I presented my formulas. However, I am mainly blogging in German. Online translators are not able to translate Excel formulas correctly and as my audience lastly increased by many English-speaking readers, I thought to write this article in English and summarize the German articles about my LAMBDAs for large numbers. The following sections about my functions are not so much detailed as the articles in German, but I hope, they will provide the basic understandings.

Large numbers in Excel

Like other spreadsheet applications, Excel comes with some limitations regarding large numbers. The precision in Excel for a specified number is confined to 15 significant digits. In practice, that means that Excel replaces all digits by zeros starting from the 16th digit, both from calculations and when entering large numbers in a cell.

Let’s look at the following example: according the IEC 80000-13 standard, one Pebibyte is equal to 1024&#8309 Bytes and therefore 1 125 899 906 842 624 Bytes. If we calculate the powers of 1024 in Excel, we get the results shown in the picture below. As you can see, the last digit for 1 PiB has been replaced in Excel by a zero.

One way to solve that problem is to use external tools, such as VBA-Add-Ins or COM-Add-Ins, which then provide user defined functions to do calculations with large numbers, stored in cells as text and not as numbers.

I developed an Excel-Add-In about 10 years ago too. The VBA-Add-In is still available on this blog and implements user defined functions in English, German and French for the addition, subtraction, multiplication and exponentiation of large numbers in Excel. The VBA-Code is protected by a password, which is "excel-ticker", without the quotes.

But now, with the release of the LAMBDA function, we are able to develop custom functions for doing calculations with large numbers by only using Excel functions.

Custom array and text functions

For implementing my custom LAMBDA functions for calculations with large numbers, I used some of my previously developed custom array and text functions. I blogged about these functions in the last weeks and additionally wrote a summary for my array functions in English here. The used functions are the following:

ARRAY.FROMTEXT(Value)
ARRAY.FROMTEXT.EX(Value,Blocksize,Direction,FillCharacter,AdditionalRows)
ARRAY.PUSH(Array,Defaults)
ARRAY.REVERSE(Array)

TEXT.TRIM.LEFT(Value;Character)

ARRAY.FROMTEXT creates a dynamic array of the given text value by just putting each character of the text into an array element. ARRAY.FROMTEXT.EX is an extended version of ARRAY.FROMTEXT and creates a dynamic array from the given text, where each item of the array has a length of Blocksize characters. Shorter subtexts a padded by the character specified by the argument FillCharacter and Direction specifies the direction from where the text is split. If Direction is equal to 2, then the text is split from right to left. The argument AdditionalRows specifies if additional rows should be created in the array, which are also padded with the fill character.

ARRAY.PUSH expects two arguments: the first one is the array, where each row should be copied to the next row, what leads to that the last row is dropped. The values of the first row are overwritten by the value specified in the second argument of the function.

ARRAY.REVERSE simply reverses an array, so that the last item in the array becomes the first item and the first item the last item.

The function TEXT.TRIM.LEFT removes the specified character from the left side of the text, until the next character does not match the character to be removed. Example: TEXT.TRIM.LEFT("000123", "0") returns „123“.

LNUM.SUM

Let’s assume, the two large numbers 99 995 492 198 785 672 356 and 7 392 345 623 648 574 are stored (without the spaces) in two cells in a worksheet. If we would like to add both numbers, we could implement a function, which adds each digit from the right to the left and considers a carry value. That would make in this example at least 16 iterations until we are done.

A faster way is to split the numbers into blocks, add each corresponding block, consider carry values and reassemble the result. As each block is so small in length, that it can be taken as number in Excel and therfore be added by a simple sum, that will save us many operations. However, we should find some logic how to handle the carry values.

The following figure shows how the numbers X and Y are split into blocks of 4 digits and then added. A carry value appears, if the sum of the blocks in one column is greater than 10 000. The carry value is then added to the next block.

Of course, if is also possible to use another bloc size, e.g eight or ten. The only requirement is that the block size should be smaller than 15, as that is the precision used in Excel. The function LNUM.ADD defines as follows:

=LNUM.ADD(Value1,Value2,Blocksize)

I opted to that the argument Blocksize should be optional. That means, that in the LAMBDA function, a validation should be done for that argument.

The first step is to create dynamic arrays from the two numbers and inverse the arrays. Then, we can calculate the sum of each block. The following picture illustrates the results.

For splitting both numbers into arrays, I used my custom function ARRAY.FROMTEXT.EX, that also adds padding zeros to the shorter number. Then I used ARRAY.REVERSE to reverse the arrays. The formulas in C2 and D2 are:

C2=LET(Value1,B2,Value2,B3,Blocksize,B4,
       L,ROUNDUP(MAX(LEN(0&Value1),LEN(0&Value2))/Blocksize,0),
       X,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value1,Blocksize,2,0,L-
         ROUNDUP(LEN(0&Value1)/Blocksize,0))),X)

D2=LET(Value1,B2,Value2,B3,Blocksize,B4,
       L,ROUNDUP(MAX(LEN(0&Value1),LEN(0&Value2))/Blocksize,0),
       Y,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value2,Blocksize,2,0,L-
         ROUNDUP(LEN(0&Value2)/Blocksize,0))),Y)

We should ensure, that both generated arrays have the same number of rows. For that, we use the variable L to calculate the number of blocks for each large number and retriev the maximum number.Note, that the values for the large numbers have been additionally prepended by a zero to avoid empty cells. L is then used to calculate the additional rows within ARRAY.FROMTEXT.EX. The arrays are then reversed.

Adding the two arrays is simple: we just write X+Y or better –X–Y for enforcing a conversion of the strings into numbers. Putting the formulas from C2 and D2 together and calculating the sum results in E2:

E2=LET(Value1,B2,Value2,B3,Blocksize,B4,
       L,ROUNDUP(MAX(LEN(0&Value1),LEN(0&Value2))/Blocksize,0),
       X,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value1,Blocksize,2,0,L-
         ROUNDUP(LEN(0&Value1)/Blocksize,0))),
       Y,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value2,Blocksize,2,0,L-
         ROUNDUP(LEN(0&Value2)/Blocksize,0))),
       Z,TEXT(--X--Y,REPT(0,Blocksize+1)),Z)

The result Z is additionally formatted as 5-digit number. The first digit – either 1 or 0 – is then the carry values. So, let’s split Z into two arrays: a left one, which has 1 character representing the carry values and a right one which holds the sums. That gives us two formulas:

F2=LET(Array,E2#,LEFT(Array,1))
G2=LET(Array,E2#,Blocksize,B4,RIGHT(Array,Blocksize))

The left array contains the values 1-1-0-1-0-0. For being added to the next block, we should push the values by one row, so that we get 0-1-1-0-1-0 and then add the values to the right array. This gives us the following formulas:

H2=ARRAY.PUSH(F2#,0)
I2=LET(Array1,G2#,Array2,H2#,Blocksize,B4,
   TEXT(--Array1--Array2,REPT(0,Blocksize+1)))

If we put these formulas all together, we obtain:

F2=LET(Data,E2#,Blocksize,B4,
       C,LEFT(Data,1),
       L,ARRAY.PUSH(C,0),
       R,RIGHT(Data,Blocksize),
         TEXT(--L--R,REPT(0,Blocksize+1)))

At this point, we should consider, that adding a carry value could result in a new carry value. For example, there is one block in column G which contains 9999 and a carry value that should added. The result is 10 000 in I6, what we lead to add a new carry to the next block in I7.

That means, that we should do an iteration for the carry values. The iteration can stop, if all carry values – their sum – is equal to zero. In our case, the iteration can exit on the third iteration.

So, let’s create the custom function LNUM.SUM.ITERATOR for the iteration, which follows the logic described above. This function needs two arguments: the first one is an array of values and the second one the block size. As LNUM.SUM.ITERATOR is a recursive function; the results of each iteration will serve as input values for the next iteration. The LAMBDA function defines as follows:

=LAMBDA(Data,Blocksize,
 LET(C,LEFT(Data,1),
     L,ARRAY.PUSH(C,0),
     R,RIGHT(Data,Blocksize),
     IF(SUM(--C)<1,Data,
     LNUM.SUM.ITERATOR(TEXT(--L--R,
     REPT(0,Blocksize+1)),Blocksize))))

The IF statement checks whether the sum of the carry values is zero or not. If that is the case, the function returns the unchanged input array. If not, then the function calls itself and passes the sum of the left and right arrays L and R. After defining the name LNUM.SUM.ITERATOR in the name manager, we can write in F2 the formula:

F2=LNUM.SUM.ITERATOR(E2#,B4)

That gives us the result of the multiplication of the two large numbers. We then just need to concatenate the reversed array items to get the final result.

The formula for concatenating the values in column F is:

=LET(Data,F2#,Blocksize,B4,
     T,CONCAT(ARRAY.REVERSE(RIGHT(Data,Blocksize))),T)

Now, we can put all togther and create the LAMBDA function:

=LAMBDA(Value1,Value2,Blocksize,
 LET(B,IF(OR(ISBLANK(Blocksize),NOT(ISNUMBER(Blocksize))),10,
       IF(Blocksize>14,14,Blocksize)),
     L,ROUNDUP(MAX(LEN(0&Value1),LEN(0&Value2))/B,0),
     X,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value1,B,2,0,L-
       ROUNDUP(LEN(0&Value1)/B,0))),
     Y,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value2,B,2,0,L-
       ROUNDUP(LEN(0&Value2)/B,0))),
     Z,LNUM.SUM.ITERATOR(TEXT(--X--Y,REPT(0,B+1)),B),
     T,TEXT.TRIM.LEFT(CONCAT(ARRAY.REVERSE(RIGHT(Z,B))),0),
       IF(LEN(T)>0,T,0)))

Additionally, the formula meets the requirement, that the block size should be optional. That is done in variable B, which checks whether Blocksize is set or smaller than 15. If not, a block size of 10 is used. The variable T additionally removes all leading zeros from the result. And the last calculation checks whether the result in T has a length greater than zero. If not, then zero is returned.

We can now add the new name LNUM.SUM in the name manager and assign the LAMBDA function to that name.

LNUM.COMPARE

The custom function LNUM.COMPARE compares two positive large numbers and returns -1 if the first value is smaller than the second one and +1 if the first value is greater than the second one. In case both values are equal, the function returns 0. The syntax is:

=LNUM.COMPARE(Value1,Value2)

There are several possibilities for comparing two large numbers stored as text. One possibility is to first bring both numbers to the same length by prepending a zero to the shorter one and then recursively compare each digit from left to right.

Another possibility is to also bring the numbers to the same length, but then create arrays from the numbers and search the first position within the array where the digits differ. This method does not need a recursion and is the method I have chosen for the custom function.

For creating an array of each value, I used my array function ARRAY.FROMTEXT. This gives us the following LET formulas in C2 and D2:

C2=LET(Value1,B2,Value2,B3,
       L,MAX(LEN(0&Value1),LEN(0&Value2)),
       X,ARRAY.FROMTEXT(REPT(0,L-LEN(0&Value1))&0&Value1),X)
D2=LET(Value1,B2,Value2,B3,
       L,MAX(LEN(0&Value1),LEN(0&Value2)),
       Y,ARRAY.FROMTEXT(REPT(0,L-LEN(0&Value2))&0&Value2),Y)

The variable L calculates the maximum length of both numbers. The variables X and Y are the resulting dynamic arrays created by ARRAY.FROMTEXT in column C and D. Note, that I prepended an additional zero to each value for avoiding empty cells.

Both formulas can now be merged into a single formula, which also performs the comparison between the values in X and Y. As X and Y are arrays, writing Y <> X will give us an array of TRUE and/or FALSE values. Then, we can search in that array the first occurrence of TRUE by using the MATCH function and store the result in the variable P. If no match is found for TRUE, we should set P equal to 1. Lastly, we compare the values from X and Y at position P and return the desired values -1, +1 or 0. The formula is:

E2=LET(Value1,B2,Value2,B3,
       L,MAX(LEN(0&Value1),LEN(0&Value2)),
       X,ARRAY.FROMTEXT(REPT(0,L-LEN(0&Value1))&0&Value1),
       Y,ARRAY.FROMTEXT(REPT(0,L-LEN(0&Value2))&0&Value2),
       P,IFERROR(MATCH(TRUE,X<>Y,0),1),
         IF(--INDEX(X,P,1)>--INDEX(Y,P,1),1,
         IF(--INDEX(X,P,1)<--INDEX(Y,P,1),-1,0)))

That’s it. Now, we can write our LAMBDA function, add the new name LNUM.COMPARE to the name manager and assign the LAMBDA formula:

=LAMBDA(Value1,Value2,
 LET(L,MAX(LEN(0&Value1),LEN(0&Value2)),
     X,ARRAY.FROMTEXT(REPT(0,L-LEN(0&Value1))&0&Value1),
     Y,ARRAY.FROMTEXT(REPT(0,L-LEN(0&Value2))&0&Value2),
     P,IFERROR(MATCH(TRUE,X<>Y,0),1),
       IF(--INDEX(X,P,1)>--INDEX(Y,P,1),1,
       IF(--INDEX(X,P,1)<--INDEX(Y,P,1),-1,0))))

LNUM.MAX

We can use LNUM.COMPARE to create a function which returns the maximum of both values compared. The syntax of the function is:

=LNUM.MAX(Value1,Value2)

And its LAMBDA function defines as follows:

=LAMBDA(Value1,Value2,
        IF(LNUM.COMPARE(Value1,Value2)>0,Value1,Value2))

LNUM.MIN

Like for LNUM.MAX, we can use LNUM.COMPARE to create a function which returns the minimum of both values compared. The syntax of the function is:

=LNUM.MIN(Value1,Value2)

And its LAMBDA function defines as:

=LAMBDA(Value1,Value2,
        IF(LNUM.COMPARE(Value1,Value2)<0,Value1,Value2))

LNUM.SUBTRACT

The algorithm for subtracting two large numbers is similar to the algorithm for adding two large numbers. In a first step, the numbers are split into blocks. Then, partial calculations are made, which should also consider carry values. The last step is to reassemble the blocks.

A subtraction can be traced back to an addition of the minuend (X) and the ten's complement of the subtrahend (Y). The ten's complement is formed by the difference of each digit to the number 9 and adding 1 to the result. By following this logic, a subtraction can be formulated as "next power of ten + minuend - subtrahend - carry = result with or without a carry value for the next block".

Note, that for getting this to work correctly, the two numbers must be positive, and that the minuend must be greater than or equal to the subtrahend. The figure above exemplary shows how two numbers X and Y are split into 4-digit blocks and subtracted. As you can see, a carry of -1 moves to the next block as soon as the result in the current block is less than 10 000.

The function LNUM.SUBTRACT defines as follows:

=LNUM.SUBTRACT(Value1,Value2,Blocksize)

The argument Blocksize should be optional. And the function should be able to recognize the case where the minuend is smaller that the subtrahend, inverse the subtraction and add a minus sign to the result.

So, the first step is to split the numbers into arrays and create an array of powers of ten. The following screenshot illustrate how the arrays look like when using a block size of 4 characters. Column F shows the calculation done for each row.

Let's look at the LET formulas in C2, D2 and E2, which are:

C2=LET(Value1,B2,Value2,B3,Blocksize,B4,
       L,ROUNDUP(MAX(LEN(0&Value1),LEN(0&Value2))/Blocksize,0),
       X,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value1,Blocksize,2,0,L-
         ROUNDUP(LEN(0&Value1)/Blocksize,0))),X)

D2=LET(Value1,B2,Value2,B3,Blocksize,B4,
       L,ROUNDUP(MAX(LEN(0&Value1),LEN(0&Value2))/Blocksize,0),
       Y,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value2,Blocksize,2,0,L-
         ROUNDUP(LEN(0&Value2)/Blocksize,0))),Y)

E2=LET(Value1,B2,Value2,B3,Blocksize,B4,
       L,ROUNDUP(MAX(LEN(0&Value1),LEN(0&Value2))/Blocksize,0),
       Z,SEQUENCE(L,1,1,0)*10^Blocksize,Z)

The variable L calculates the maximum length of both numbers, prepended by zero to avoid empty cells. After that, L is rounded up to the next multiple of the chosen block size. Then, I used my custom function ARRAY.FROMTEXT.EX to create an array of the large number padded with zeros. The direction is set to 2, what will start to split the string from the right. Finally, the array is reversed, as the subtraction should be made from right to left. The formula in E2 creates an array of powers of ten by using the Excel function SEQUENCE.

Let's put all formulas together in column F and also calculate Z+X-Y:

F2=LET(Value1,B2,Value2,B3,Blocksize,B4,
       L,ROUNDUP(MAX(LEN(0&Value1),LEN(0&Value2))/Blocksize,0),
       X,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value1,Blocksize,2,0,L-
         ROUNDUP(LEN(0&Value1)/Blocksize,0))),
       Y,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value2,Blocksize,2,0,L-
         ROUNDUP(LEN(0&Value2)/Blocksize,0))),
       Z,SEQUENCE(L,1,1,0)*10^Blocksize,
       S,TEXT(Z--X---Y,REPT(0,Blocksize+1)),S)

The results in column F are formatted as five-digit numbers. The first character from the left is now the decision criteria if -1 should be added to the next block or not. For example, the first value is 03872, which is smaller than 10 000 and therefore leads to an addition of -1 to 16 203 which then become 16 202. The value 16 203 is greater than 10 000 and so we don't need to add a carry value to the next block.

Therefore, the logic for the next calculation step is to split the array values in column F by taking from the left side 1 character and 4 characters from the right side. Then, an IF statement will allow to reverse all zeros and ones. After that, pushing all values by one position down will give us all carry values to be subtracted from the right array. In our example, these steps will lead to the following transition for the left array: from 0-1-0-0-1-1 to 1-0-1-1-0-0 to 0-1-0-1-1-0.

By using my custom array function ARRAY.PUSH, the formula would be:

ARRAY.PUSH(IF(--LEFT(F2#,1)>0,0,1),0)

Putting all that together and calculating the result of the subtraction gives us the following formula:

=LET(Value1,B2,Value2,B3,Blocksize,B4,
     L,ROUNDUP(MAX(LEN(0&Value1),LEN(0&Value2))/Blocksize,0),
     X,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value1,Blocksize,2,0,L-
       ROUNDUP(LEN(0&Value1)/Blocksize,0))),
     Y,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&Value2,Blocksize,2,0,L-
       ROUNDUP(LEN(0&Value2)/Blocksize,0))),
     Z,SEQUENCE(L,1,1,0)*10^Blocksize,
     S,TEXT(Z--X---Y,REPT(0,Blocksize+1)),
     U,ARRAY.PUSH(IF(--LEFT(S,1)>0,0,1),0),
     V,RIGHT(S,Blocksize),
     T,TEXT.TRIM.LEFT(CONCAT(ARRAY.REVERSE(
       TEXT(--V-U,REPT(0,Blocksize)))),0),
       IF(LEN(T)>0,T,0))

As before, the variable S calculates Z+X-Y. The variable U calculates the carry values and V is the right side from the splitting of S. The variable T subtracts the carry values U from V and then formats the results as 4-digit values. After that the array is reversed and its values concatenated. It is possible that the result contains leading zeros, which are removed by using my custom function TEXT.TRIM.LEFT. Lastly, the result in T is checked for a length greater than zero. If that is not the case, 0 is returned.

We could stop here and create the LAMBDA function. But a requirement is that the custom function allows an empty value for the block size and should recognize if the minuend is smaller than the subtrahend. For that, we add two new variables to the LET formula. The final LAMBDA function is:

=LAMBDA(Value1,Value2,Blocksize,
 LET(B,IF(OR(ISBLANK(Blocksize),NOT(ISNUMBER(Blocksize))),10,
       IF(Blocksize>14,14,Blocksize)),
     C,LNUM.COMPARE(0&Value1,0&Value2),
     L,ROUNDUP(MAX(LEN(0&Value1),LEN(0&Value2))/B,0),
     X,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&IF(C<0,Value2,Value1),B,2,0,L-
       ROUNDUP(LEN(0&IF(C<0,Value2,Value1))/B,0))),
     Y,ARRAY.REVERSE(ARRAY.FROMTEXT.EX(0&IF(C<0,Value1,Value2),B,2,0,L-
       ROUNDUP(LEN(0&IF(C<0,Value1,Value2))/B,0))),
     Z,SEQUENCE(L,1,1,0)*10^B,
     S,TEXT(Z--X---Y,REPT(0,B+1)),
     U,ARRAY.PUSH(IF(--LEFT(S,1)>0,0,1),0),
     V,RIGHT(S,B),
     T,TEXT.TRIM.LEFT(CONCAT(ARRAY.REVERSE(
       TEXT(--V-U,REPT(0,B)))),0),
       IF(LEN(T)>0,IF(C<0,"-","")&T,0)))

The variable B sets a default value for the block size, if one of the conditions is not met. C uses the custom function LNUM.COMPARE to compare the two numbers and inverses the arguments, if needed.

LNUM.PRODUCT

There are several algorithms for performing a multiplication of very large numbers while consuming as few calculation steps as possible and therefore as little time as possible. A very fast algorithm is, for example, the Schönhage-Strassen algorithm, which is based on a discrete Fourier transform. However, implementing a Fourier transformation in Excel by using only formulas would be in my opinion extremely complicated, if it would be feasible at all.

Another very fast algorithm is the Karatsuba algorithm, which replaces a multiplication of two large numbers X and Y by three multiplications of smaller numbers, each with about half as many digits as X and Y, plus some additions and digit shifts. A big advantage of the Karatsuba algorithm is, that this algorithm can be used recursively and looks like it is made for a LAMBDA function.

The following picture illustrates how the two numbers X and Y are decomposed and which formulas are used for performing the calculation.

The first step is to calculate the maximum length N of both numbers X and Y. The result should be divisible by 2 and therefore, if the length is odd, +1 added to it. Then the left and rights parts XL, XR, YL and YR of the are retrieved from X and Y. Each part has a length of N/2.

Then XS and YS are calculated as sums of the parts. These numbers could be large numbers too and therefore the Excel formula should use the custom function LNUM.SUM.

The next step is to calculate the products PA, PB and PC. After that, the final result of the multiplication of X and Y is calculated by the formula F, which also includes subtractions and therefore should use the custom function LNUM.SUBTRACT.

So, let's build the Excel formulas and let's assume, that our two large numbers are stored in B2 and C2, like shown in the screenshot below.

The calculation of N is not very difficult:

B3=LET(Value1,B2,Value2,C2,
       M,MAX(LEN(Value1),LEN(Value2)),
       N,M+MOD(M,2),N)

The formula in C3 is the same as in B3. Row 4 shows both values X and Y, where the shorter number has been padded with zeros. We can take these numbers and split them into arrays by using the MID and SEQUENCE function. So, we get in B5 and C5 and obtain the arrays X and Y:

B5=LET(Value1,B2,Value2,C2,
       M,MAX(LEN(Value1),LEN(Value2)),
       N,M+MOD(M,2),
       X,MID(REPT(0,N-LEN(Value1))&Value1,SEQUENCE(2,1,1,N/2),N/2),X)

C5= LET(Value1,B2,Value2,C2,
       M,MAX(LEN(Value1),LEN(Value2)),
       N,M+MOD(M,2),
       Y,MID(REPT(0,N-LEN(Value2))&Value2,SEQUENCE(2,1,1,N/2),N/2),Y)

Retrieving XL, XR, YL and YR is now simple, as we can use the INDEX function for that:

B7 =LET(X,B5#,XL,INDEX(X,1,1),XL)
B8 =LET(X,B5#,XR;INDEX(X,2,1),XR)
B9 =LET(Y,C5#,YL;INDEX(Y,1,1),YL)
B10=LET(Y,C5#,YR;INDEX(Y,2,1),YR)

XS and YS are defined as sums for the different parts. By using LNUM.SUM, we get for these values:

B11=LET(XL,B7,XR,B8 ,XS,LNUM.SUM(XL,XR,8),XS)
B12=LET(YL,B9,YR,B10,YS,LNUM.SUM(YL,YR,8),YS)

Now, it is time to calculate the products PA, PB and PC. As the we can still have large numbers here, we should use our custom function LNUM.PRODUCT as a recursion:

PA=XL*YL=LNUM.PRODUCT(XL,YL)
PB=XR*YR=LNUM.PRODUCT(XR,YR)
PC=XS*YS=LNUM.PRODUCT(XS,YS)

However, we are just developing our function LNUM.PRODUCT. So, we can not use the function yet. Therefore, we calculate these multiplications manually and temporarily store the results in the cells shown below.

The next step is to implement the formula for calculating the total result. The formula is PA*10^N+(PC-PB-PA)*10^(N/2)+PB and includes two subtractions. However, we can rewrite this formula to PA*10^N+(PC-(PB+PA))*10^(N/2)+PB and reduce the subtractions to one. It seems to be useful to introduce some helper variables:

TA=PA+PB
TB=PC-TA & REPT(0;N/2)
TC=PB+TB
TR=TC+(PA & REPT(0;N))

It should be noted that we are working with large numbers and consider that when creating large numbers equal to a power of ten. The formulas for TA, TB, TC and TR are then:

TA=LET(PA,B13,PB,B14,LNUM.SUM(PA,PB,8))
TB=LET(N ,B3 ,PC,B15,TA,B16,LNUM.SUBTRACT(PC,TA,8)&REPT(0,N/2))
TC=LET(PB,B14,TB,B17,LNUM.SUM(PB,TB,8))
TR=LET(N ,B3 ,TC,B18,PA,B13,LNUM.SUM(TC,PA&REPT(0,N),8))

The final step is to put it all together. At some point in the recursive calls of LNUM.PRODUCT for the calculation of PA, PB and PC will lead to numbers, which can be multiplied by a simple multiplication in Excel. The final formula should consider this in an IF statement.

This gives us the LAMBDA function:

=LAMBDA(Value1,Value2,
        LET(M,MAX(LEN(Value1),LEN(Value2)),
            N,M+MOD(M,2),
            IF(N<5,PRODUCT(--Value1,--Value2),
               LET(X ,MID(REPT(0,N-LEN(Value1))&Value1,
                      SEQUENCE(2,1,1,N/2),N/2),
                   Y ,MID(REPT(0,N-LEN(Value2))&Value2,
                      SEQUENCE(2,1,1,N/2),N/2),
                   XL,INDEX(X,1,1),
                   XR,INDEX(X,2,1),
                   YL,INDEX(Y,1,1),
                   YR,INDEX(Y,2,1),
                   XS,LNUM.SUM(XL,XR,8),
                   YS,LNUM.SUM(YL,YR,8),
                   PA,LNUM.PRODUCT(XL,YL),
                   PB,LNUM.PRODUCT(XR,YR),
                   PC,LNUM.PRODUCT(XS,YS),
                   TA,LNUM.SUM(PA,PB,8),
                   TB,LNUM.SUBTRACT(PC,TA,8)&REPT(0,N/2),
                   TC,LNUM.SUM(PB,TB,8),
                      LNUM.SUM(TC,PA&REPT(0,N),8)))))

Please note, that LNUM.SUBTRACT assumes that PC is always greater than TA. I guess that this will be always the case; at least I did not find a case in my tests, where PC was smaller than TA. But, if I am wrong, then the multiplication of the two large number will fail and return an error. Then, it would be possible to adapt the formula for handling that.

Conclusion

Awesome, what can be done with LAMBDA functions.

I did several tests with my functions, where I randomly generated large numbers having at least 100 and max 300 digits. With up to ten thousand of additions of large numbers, Excel remained very fast and the results were calculated under 5 seconds. By nature, a multiplication is of course more time consuming and about 1 000 multiplications of very large numbers took between 30 and 240 seconds to calculate, depending on how many large numbers with the maximum length were generated.

As a last note, some words about the naming of my functions. You surely noticed that my functions all have a dot in it. The reason is to ensure a better readability and a kind of categorizing. Dots are allowed in Excel names. However, the LAMBDA function is very new in Excel and still in Beta. That means, that the Microsoft Excel Team can still change requirements and naming conventions for custom LAMBDA functions. For example, they can disallow a dot in names in the future. However, you can rename at any time the functions mentioned in this article. So, I think, that's not a problem.

Happy Exceling :-)