Cumulative values and MAKEARRAY does not work with LET

Cumulative values and MAKEARRAY does not work with LET

Problem Description:

Given a list, I would like to make a list of cumulative values:

enter image description here

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.

enter image description here

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;


Or =SCAN(0,F5:J5,LAMBDA(a,b,a+b))

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.