Summing up to a point

Hi guys,

Short question. I have an array of values, returned by using filter function which is {20,30,40,10,60}. I would like to deduct 60 from this such that the output is {0,0,30,10,60}. Is there a way to do this? I was using scan and lambda function but residuals are accumulated. (Results were {0,0,30,40,100})

I'm afraid I don't understand SCAN and LAMBDA... yet... but try:

=LET(a,MMULT(N(SEQUENCE(ROWS(B2#))>=SEQUENCE(,ROWS(B2#))),N(+B2#))-SEQUENCE(5,,60,0),IF(a<0,0,a))

or:

=LET(d,B2#,r,ROWS(d),a,MMULT(N(SEQUENCE(r)>=SEQUENCE(,r)),N(+d))-SEQUENCE(r,,60,0),IF(a<0,0,a))

Or:

=LET(a,FILTER(A2:A6,A2:A6>0),IF(SCAN(C2,a,LAMBDA(x,y,x+y))<0,0,SCAN(C2,a,LAMBDA(x,y,x+y))))

EDIT: Ignore! I misunderstood the requirement.

Hahaha. Trying to do 3 things at once.... this is a mess... sorry... but the other 2 are going fine though (not Excel-related)!!

Please explain why the third value should be 30.

(20+30+40)-60. From that point onwards.. return the value from column B.

A bit convoluted... but:

=LET(a,B2#,d,C2,b,SUBTOTAL(9,OFFSET(B2,,,SEQUENCE(ROWS(B2#))))+d,IF(b<0,0,IF(b<a,b,a)))

or, non-volatile:

=LET(a,B2#,d,C2,b,MMULT(N(SEQUENCE(ROWS(B2#))>=SEQUENCE(,ROWS(B2#))),N(+B2#))+d,IF(b<0,0,IF(b<a,b,a)))

and of course, you can drop your incomprehensible SCAN/LAMBDA into that, too:

=LET(a,B2#,d,C2,b,SCAN(d,a,LAMBDA(x,y,x+y)),IF(b<0,0,IF(b<a,b,a)))

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.