Excel formula errors when not referencing a physical array

Question

I am trying to return the number of months of storage where the sum of all previous months is equal to the one time removal rate in one cell using the values below:

I have come to the following solution of creating an array with a length of 12 of the applicable monthly rates. Which is as follows {.5,.5,.5,.6,.6,.6,.6,.6,.6,.9,.9,.9}

I then use the following function to find the running total for each number in the array:

LET(array,$D$9:$O$9,MAP(array,LAMBDA(x,SUM(INDEX(array,1,1):x))))

Then I can easily =match(removal rate, returned array,1) to find the index of the value I need, which is my answer since the index of the array corresponds to the months since the start of the array.

My issue is that the following formulas work when separated but don't work when combined:

Works:

LET(monArray,SEQUENCE(1,12),IFS(monArray<=3,0.5,monArray<=9,0.6,monArray<=12,0.9))
MATCH(D4,LET(array,$D$9:$O$9,MAP(array,LAMBDA(x,SUM(INDEX(array,1,1):x)))),1)

Does not work:

=LET(ratearray,LET(monArray,SEQUENCE(1,12),IFS(monArray<=3,0.5,monArray<=9,0.6,monArray<=12,0.9)),MATCH(D4,LET(array,ratearray,MAP(array,LAMBDA(x,SUM(INDEX(array,1,1):x)))),1))

I believe I have found the issue which is that the LAMBDA function has issues when not referencing an actual array and instead referencing an alias or formula.

Works:

MATCH(6,MAP($D$9:$O$9,LAMBDA(x,SUM(INDEX($D$9:$O$9,1):x))),1)

Does not work:

MATCH(6,MAP(VSTACK($D$9:$O$9),LAMBDA(x,SUM(INDEX(VSTACK($D$9:$O$9),1):x))),1)

The use of: VSTACK($D$9:$O$9) breaks the lambda function.

I am open to fixes that fix the issues using my formula and provide a work around for the LAMBDA limitation or fixes that completely rewrite my formulas to provide the original result.

I am not open to VBA answers I think this can be done without and the final destination of this formula will not allow for a Macro Enabled Worksheet.

Scott Craner · Accepted Answer · 2024-02-13 23:22:42Z

2

The problem is that you are trying to use a range reference when using INDEX(array,1,1):x That is going to require a Range and not an array. The array does not have an address to convert to a range.

But there is a better formula called SCAN that will build the sum array that does not rely on using a range:

=MATCH(D4,SCAN(0,D9:O9,LAMBDA(a,b,a+b)),1)

Because it loops the array and does not require a range reference built we can use your array builder:

=MATCH(D4,SCAN(0,LET(monArray,SEQUENCE(1,12),IFS(monArray<=3,0.5,monArray<=9,0.6,TRUE,0.9)),LAMBDA(a,b,a+b)),1)

edited Feb 13, 2024 at 23:22

answered Feb 13, 2024 at 22:58

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BSlice Over a year ago

Perfect, I knew there was information missing from my Index function but I didn't know what it was AND you rewrote the complete function thank you sir! I will implement this tomorrow.

Collectives™ on Stack Overflow

Excel formula errors when not referencing a physical array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related