Cumulative values and MAKEARRAY does not work with LET
Problem Description:
Given a list, I would like to make a list of cumulative values:
I have created a formula which works =MAKEARRAY(1;5;LAMBDA(r;c;SUM(INDEX(F5:J5;1;1):INDEX(F5:J5;1;c)))).
However, I realize that by wrapping it with LET the formula =LET(ps; F5:J5; MAKEARRAY(1;COLUMNS(ps);LAMBDA(r;c;SUM(INDEX(ps;1;1):INDEX(ps;1;c))))) returns a list of #VALUE.
Does anyone know the reason?
Additionally, does anyone have a better idea to make such a cumulative list by Excel formulas (my solution is not optimal)? If we don’t use the LAMBDA function and the helper functions, that will be even better.
Solution – 1
When using a range in LET it is no longer stored as a range, but as an array in memory.
The position in the sheet is no longer relevant. But when you use INDEX(start,1,1):INDEX(end,1,1) references, the position is relevant, because where in the sheet would for instance start be, and where would end be? By using LET you tell Excel to no longer store it’s position and you therefore can use the individual values in each "range", but you can’t refer to anything in between the two.
Instead you could better use SCAN for this;
=SCAN(0,SEQUENCE(1,5),LAMBDA(a,b,a+b))
Or =SCAN(0,F5:J5,LAMBDA(a,b,a+b))