MakeArray to dynamically Offset rows by a factor on the source row

Question

I have a table of invoice which we have monthly amounts we expect to bill. We also have a factor on how long many months we expect to actually collect per invoice.

What I am attempting to do is take the monthly values array and reconstruct into into a new monthly table where the given invoice will be offset by the number of months delayed we expect to collect.

I have spill formulas that are calculating and determining how much wider the months need to go (ie: data ends in Dec 2026, but we expect our last collection to be in March 2026, therefore my formula will take the time period and extend 2 more periods).

The part I'm struggling with is how to write a Let/MakeArray formula that is able to shift the data over to the right by the number of months in the row. The closest formula I got would shift the data incorrectly to the left, I tried to use Hstack and sequence to fill in the x number of cells on the left with 0.

=LET(data,$C$5:$N$9, 
height,SEQUENCE(COUNTA($B$20#)),
width,SEQUENCE(,COUNTA($C$19#)),
mfactor,$A$20#,
datarange, IFERROR(INDEX(data,height,width),0),
final,MAKEARRAY( ROWS(height), COLUMNS(width), LAMBDA(r,c, INDEX(DROP(datarange,,INDEX(mfactor,r)),r,c))),
IFERROR(final,0)
)

I've also tried, but this yielded a single cell 0 output:

=LET(data,$C$5:$N$9, 
height,SEQUENCE(COUNTA($B$21#)),
width,SEQUENCE(,COUNTA($C$20#)),
mfactor,$A$21#,
datarange, IFERROR(INDEX(data,height,width),0),
final,MAKEARRAY( ROWS(height), COLUMNS(width), LAMBDA(r,c, HSTACK( TRANSPOSE(SEQUENCE(INDEX(mfactor,r),0,0,0)), INDEX(DROP(datarange,,INDEX(mfactor,r)),r,c)   ))),
IFERROR(final,0)
)

In this image you will see the data source table at the top, the expected output in the middle, and what I tried and it's results at the bottom.

Mfactor	Invoice	1/1/2025	2/1/2025	3/1/2025	4/1/2025	5/1/2025	6/1/2025	7/1/2025	8/1/2025	9/1/2025	10/1/2025	11/1/2025	12/1/2025
1	1	100	100	100	100	100	100	100	100	100	100	100	100
1	2	50	50	50	50	50	50	50	50	50	50	50	50
2	3	150	150	150	150	150	150	150	150	150	150	150	150
1	4	60	60	60	60	60	60	60	60	60	60	60	60
2	5	100	100	100	100	100	100	100	100	100	100	100	100

P.b · Accepted Answer · 2025-10-23 20:35:58Z

2

The same different:

=MAKEARRAY(ROWS(B21#),COUNT(C20#),
    LAMBDA(r,c,
           XLOOKUP(EDATE(INDEX(C20#,,c),
                         -INDEX(A21#,r,)),
                   C20#,
                   INDEX(C5:N9,r,),
                   0)))

It looks up the header month minus current rows Mfactor-months. If not found it returns 0, else that month's value.

Or if you don't need it to spill all in one go, you can use this in the first row/column to spill sideways only and fill down:

=XLOOKUP(EDATE(+C$20#,-A21),C$20#,C5:N5,0)

edited Oct 23 at 20:35

answered Oct 23 at 18:58

P.b

12k2 gold badges15 silver badges34 bronze badges

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

Comments

Mark S. · Accepted Answer · 2025-10-23 14:35:48Z

0

Okay, I managed to figure this out. So what was happening is I was trimming the columns from the right instead of padding them from the left

=LET(data,$C$5:$N$9, 
height,SEQUENCE(COUNTA($B$21#)),
width,SEQUENCE(,COUNTA($C$20#)),
mfactor,$A$21#,
datarange, IFERROR(INDEX(data,height,width),0),
final,MAKEARRAY( ROWS(height), COLUMNS(width), 
   LAMBDA(r,c,  
    IF( c<=INDEX(mfactor,r),0,INDEX(datarange,r,c-INDEX(mfactor,r)) ) ) ),
IFERROR(final,0) )

This got around the errors I was encountering. Essentially my issue was that as I was using hstack and similar array style functions whereas the makearray I needed to think of a formula that each individual cell could handle.

edited Oct 23 at 14:35

answered Oct 23 at 14:09

Mark S.

2,8661 gold badge10 silver badges30 bronze badges

3 Comments

DjC Oct 26 at 17:44

Simplified variant: =MAKEARRAY(ROWS(A21#),COLUMNS(C20#),LAMBDA(r,c,LET(j,c-INDEX(A21#,r),IF(j,IFERROR(INDEX(C5:N9,r,j),0),0)))) Alternative method, without MakeArray: =LET(j,SEQUENCE(,COLUMNS(C20#))-A21#,IF((j>0)*(j<=COLUMNS(C5:N9)),INDEX(C5:N9,SEQUENCE(ROWS(C5:N9)),j),0))

Mark S. Oct 29 at 14:20

I really like the simplicity of the first formula you have here. Is there any advantage of not using makearray?

DjC Oct 30 at 16:08

The biggest drawback of using INDEX in an iterative manner with MAKEARRAY is that it will only be efficient when indexing range references (vs array objects). The way you've written it, datarange is an array object, so it will not scale well when used with a larger dataset. I tested yours with just 1,000 rows of data and it took almost 3 seconds to process, then compared that to mine with 10,000 rows of data, which took only 120 milliseconds. This is not something that needs to be taken into consideration with the alternative method. ;)

user22566114 · Accepted Answer · 2025-10-24 14:47:30Z

0

Alternatively you can use REDUCE and LAMBDA.

=IFNA(REDUCE(EDATE(C28,SEQUENCE(,COUNT(C28:N28)+MAX(A29:A33),0)),
SEQUENCE(ROWS(A29:A33)),LAMBDA(u,v,VSTACK(u,HSTACK(EXPAND("-",,INDEX(A29:A33,v,)),
CHOOSEROWS(C29:N33,v))))),"-")

edited Oct 24 at 14:47

answered Oct 24 at 10:26

user22566114

5464 silver badges6 bronze badges

Comments

MisterCary · Accepted Answer · 2025-10-26 21:12:17Z

0

A simple REDUCE/STACK combo with an added HSTACK and EXPAND functions added to accomplish the offset and bring it all together:

=IFNA(
    DROP(
        REDUCE(
            "",
            SEQUENCE(ROWS(A2:A6)),
            LAMBDA(a,v, VSTACK(a, HSTACK(EXPAND(0,,INDEX(A2:A6,v),0), CHOOSEROWS(C2:N6, v))))
        ),
        1
    ),
    0
)

answered Oct 26 at 21:12

MisterCary

6554 silver badges8 bronze badges

Collectives™ on Stack Overflow

MakeArray to dynamically Offset rows by a factor on the source row

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Mfactor	Invoice	1/1/2025	2/1/2025	3/1/2025	4/1/2025	5/1/2025	6/1/2025	7/1/2025	8/1/2025	9/1/2025	10/1/2025	11/1/2025	12/1/2025
1	1	100	100	100	100	100	100	100	100	100	100	100	100
1	2	50	50	50	50	50	50	50	50	50	50	50	50
2	3	150	150	150	150	150	150	150	150	150	150	150	150
1	4	60	60	60	60	60	60	60	60	60	60	60	60
2	5	100	100	100	100	100	100	100	100	100	100	100	100

Mfactor	Invoice	1/1/2025	2/1/2025	3/1/2025	4/1/2025	5/1/2025	6/1/2025	7/1/2025	8/1/2025	9/1/2025	10/1/2025	11/1/2025	12/1/2025
1	1	100	100	100	100	100	100	100	100	100	100	100	100
1	2	50	50	50	50	50	50	50	50	50	50	50	50
2	3	150	150	150	150	150	150	150	150	150	150	150	150
1	4	60	60	60	60	60	60	60	60	60	60	60	60
2	5	100	100	100	100	100	100	100	100	100	100	100	100

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

Mfactor	Invoice	1/1/2025	2/1/2025	3/1/2025	4/1/2025	5/1/2025	6/1/2025	7/1/2025	8/1/2025	9/1/2025	10/1/2025	11/1/2025	12/1/2025
1	1	100	100	100	100	100	100	100	100	100	100	100	100
1	2	50	50	50	50	50	50	50	50	50	50	50	50
2	3	150	150	150	150	150	150	150	150	150	150	150	150
1	4	60	60	60	60	60	60	60	60	60	60	60	60
2	5	100	100	100	100	100	100	100	100	100	100	100	100