Forum Discussion

VictorVidaurre's avatar
VictorVidaurre
Copper Contributor
Mar 21, 2023

Lambda #VALUE! Error

I have a simple formula to calculate the running total in a row of numbers that works as expected by itself, but generates a #VALUE! error when wrapped in a Lambda function. At this point I don't care about the running total and just want to understand the problem.

 

Assume there is a series of numbers in the cells of range A1:F1

The formula =MAKEARRAY(1, COLUMNS(A1:F1), LAMBDA(r, c, SUM(INDEX(A1:F1, 1):INDEX(A1:F1, c)))) will spill and generate a row of the running total of numbers in A1:F1, as expected.

 

However, if I wrap this formula in a Lambda, it generates an array of #Value errors:

=LAMBDA(vector, MAKEARRAY(1, COLUMNS(vector), LAMBDA(r, c, SUM(INDEX(vector, 1):INDEX(vector, c))))

 

I suspect the error is related to the colon operator inside the SUM function, but cannot figure out why it works by itself and fails inside the outer Lambda.

excel
Formulas and Functions
  • JosWoolley's avatar
    JosWoolley
    Iron Contributor
    Mar 22, 2023

    VictorVidaurre 

     

    Yes, it's an unfortunate feature of LAMBDA/MAKEARRAY constructions that MAKEARRAY forces a range passed as a parameter to LAMBDA to be first internalized to its equivalent array.

     

    Usually this is not an issue, as the vast majority of functions process ranges and arrays equivalently, though with the INDEX construction you are employing here, only a range will suffice.

     

    You can see that the issue lies with the combination of LAMBDA with MAKEARRAY, and not just with LAMBDA per se, since, for example:

     

    =LAMBDA(vector, SUM(INDEX(vector, 1):INDEX(vector, 3)))(A1:F1)

     

    is perfectly valid.

     

    For your case you could switch to SCAN:

     

    =LAMBDA(x, SCAN(0, x, LAMBDA(a, b, a + b)))(A1:F1)

     

Resources