# Cumulative values and MAKEARRAY does not work with LET

## Cumulative values and MAKEARRAY does not work with LET

Contents

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))`

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.