3

I have a continuous set of numbers in cell I32 (123456789) of my worksheet. I want to use the new LAMBDA function to add each number with nine iterations (using recursion). The final value in cell L32 should be 45.

What I've got at the moment produces a #VALUE! error. I am basing it off the =REPLACECHARS example shown at the Microsoft Excel blog, where invalid characters are stripped out of a cell.

Formula:

=LAMBDA(Number,NumberGroup,

AddNumbers(

SUM(LEFT(Number,1)),
RIGHT(NumberGroup,LEN(NumberGroup)-1)
)

)(L32)
ZygD
  • 22,092
  • 39
  • 79
  • 102
Statto
  • 410
  • 3
  • 9

1 Answers1

3

You are not using any recursion currently. You just trying to sum. If you really want recursion inside your LAMBDA() try something like:

=LAMBDA(Input,AddAll,X,IF(X=LEN(Input)+1,AddAll,Addnumbers(Input,AddAll+MID(Input,X,1),X+1)))

I created three parameters for LAMBDA():

  • Input - A reference to your cell/string of numbers;
  • AddAll - A grand total of the added numbers;
  • X - A simple counter, as if we were writing a function in VBA;

If you add the function to your name manager you can now call this using: =Addnumbers(I32,0,1), meaning:

  • Call our LAMBDA() function which we named "AddNumbers";
  • The 1st parameter must be the reference to our cell/string;
  • The 2nd parameter is our current total, zero at the start;
  • The 3rd parameter is the start of our counter, which should be 1.

I specifically added the nested IF() to get iteration going, i.o.w. recursion. The IF() checks the current state of the counter. Only if that is bigger than the total length of our input it will return the current total, otherwise; the recursion starts with calling the LAMBDA() all over again in the 2nd parameter (the FALSE parameter) where we use:

  • The same Input value;
  • Use MID() to add one of the numbers from the current index to our total of AddAll;
  • Increase our counter X by 1.

Now we have recursion out of the way, I'd say you have better options here if you want to use LAMBDA() since recursion is not needed. Try:

=LAMBDA(Input,SUM(--MID(Input,SEQUENCE(LEN(Input)),1)))

Call through =Addnumbers(I32).

JvdV
  • 70,606
  • 8
  • 39
  • 70