Sliding Summary of Values

[GoHeels]

Preference this with many thanks for recommendations to resolve my issue.

From a high level, I have a worksheet with forecasted values by retail location for 500+ stores for 12/1/2021-12/31/2021. Each store has an average number of pickup days that varies across each location. What I would like to do is forecast out the potential moving carryover by day for each store, to determine the max capacity/carryover during the period.

For example, Location A has an average pickup day value of 2, we should see on dates:
12/1/2021 = 12/1/2021
12/2/2021 = 12/1/2021+12/2/2021
12/3/2021 = 12/2/2021+12/3/2021 -------Values from 12/1 are dropped since that is outside of the average pickup window of 2 days
12/4/2021 = 12/3/2021+12/4/2021--------Values from 12/2 are dropped
12/5/2021 = 12/4/2021+12/5/2021--------Values from 12/3 are dropped


Location B has an average pickup day value of 5, so we should see on dates:
12/1/2021 = 12/1/2021
12/2/2021 = 12/1/2021+12/2/2021
12/3/2021 = 12/1/2021+12/2/2021+12/3/2021
12/4/2021 = 12/1/2021+12/2/2021+12/3/2021+12/4/2021
12/5/2021 = 12/1/2021+12/2/2021+12/3/2021+12/4/2021+12/5/2021
12/6/2021 = 12/2/2021+12/3/2021+12/4/2021+12/5/2021+12/6/2021-------Values from 12/1 are dropped since that is outside of the average pickup window of 5 days

Printscreen below with some basic examples.

The values that happen prior to 12/1 are not considered here, only 12/1 and forward, so we only want to keep the forecasted moving values from 12/1-12/31. Any help here would be greatly appreciated. Thanks in advance.

 

[Eric W]

Try:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Location	Avg Pickup Days	12/1/2021	12/2/2021	12/3/2021	12/4/2021	12/5/2021	12/6/2021	12/7/2021	12/8/2021	12/9/2021		12/1/2021	12/2/2021	12/3/2021	12/4/2021	12/5/2021	12/6/2021	12/7/2021	12/8/2021	12/9/2021
2	A	2	100	150	200	250	300	200	100	100	100		100	250	350	450	550	500	300	200	200
3	B	5	100	150	200	250	300	200	100	100	100		100	250	450	700	1000	1100	1050	950	800
4	C	1	100	150	200	250	300	200	100	100	100		100	150	200	250	300	200	100	100	100
Sheet8

Cell Formulas
Range	Formula
M2:U4	M2	=SUM(INDEX(2:2,MAX(3,MATCH(M$1,$A$1:$K$1,0)-$B2+1)):INDEX(2:2,MATCH(M$1,$A$1:$K$1,0)))

 

[GoHeels]

SUM(INDEX(2:2,MAX(3,MATCH(M$1,$A$1:$K$1,0)-$B2+1)):INDEX(2:2,MATCH(M$1,$A$1:$K$1,0)))

Awesome, worked as expected! Many thanks Eric!

 

[Eric W]

Happy to help! Thanks for the feedback. ?