Spreading Values Across Range of Dates

[ExcelUser88945]

Long time reader, first time poster.

I am looking to evenly spread my values (column C) based off of the start date (column A) and duration (column B) within my date range (column D:AB). In the case that the value isn't evenly divisible by the duration, I'd like the higher values recorded in the latter months. See row B for example. Does anyone know an easy way to automate this process?

Thanks in advance!

 

[cooleomter]

Hi morning,
Asking only if your data for duration and value is connected meaning cannot be interchange.?
if so.
1. Highlight all data of your duration and value.
2. right click your highlighted data, point to sort, then click custom sort.
3. now you choose if sorting is base on numbers of duration or Value column
4. choose your order smallest to largest if so.
5. then click ok
6.after sorting you data
7. rightclick the highlighted data
8. the click copy
9. right click on the cell where you want to paste the data.
10. on the paste option choose paste transpose.
11. now the only issue is duration data shall be paste on the first row and value data on the second row.
12. if you want to value data on first row highlight and copy only value data first then paste transpose on the first row and do it to duration data on the second row.

Hope it help.. God bless

 

[StephenCrump]

@ExcelUser88945 Welcome to the Forum!

How about:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Start	Value	Duration		Sep 2024	Oct 2024	Nov 2024	Dec 2024	Jan 2025	Feb 2025	Mar 2025	Apr 2025	May 2025	Jun 2025	Jul 2025	Aug 2025	Sep 2025	Oct 2025
3	Dec 2024	9	4					2	2	2	3
4	Feb 2025	13	3							4	4	5
5	Jun 2025	11	4											2	3	3	3
6
Sheet1

Cell Formulas
Range	Formula
E3:R5	E3	=LET(N,CEILING(B3/C3,1),M,E$2:R$2,C,COLUMNS(M),s,1+SEQUENCE(,C)-IFERROR(MATCH(EOMONTH(A3,0),EOMONTH(--M,0),),C+1),IF((s<=C3)*(s>0),N-(s<=C3*N-B3),""))
Dynamic array formulas.

 

[ExcelUser88945]

@ExcelUser88945 Welcome to the Forum!

How about:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Start	Value	Duration		Sep 2024	Oct 2024	Nov 2024	Dec 2024	Jan 2025	Feb 2025	Mar 2025	Apr 2025	May 2025	Jun 2025	Jul 2025	Aug 2025	Sep 2025	Oct 2025
3	Dec 2024	9	4					2	2	2	3
4	Feb 2025	13	3							4	4	5
5	Jun 2025	11	4											2	3	3	3
6
Sheet1

Cell Formulas
Range	Formula
E3:R5	E3	=LET(N,CEILING(B3/C3,1),M,E$2:R$2,C,COLUMNS(M),s,1+SEQUENCE(,C)-IFERROR(MATCH(EOMONTH(A3,0),EOMONTH(--M,0),),C+1),IF((s<=C3)*(s>0),N-(s<=C3*N-B3),""))
Dynamic array formulas.

You are awesome! This is exactly what I was looking for. Thanks so much!

 

[felixstraube]

Just for fun, another option:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2	Start	Duration	Value		Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25
3	Dec-24	4	23					5	6	6	6
4	Feb-25	10	24							2	2	2	2	2	2	3	3	3	3
5	Jun-25	4	11											2	3	3	3
6
Sheet1

Cell Formulas
Range	Formula
E3:K3,E5:Q5,E4:S4	E3	=LET(d, B3, v, C3, s, SEQUENCE(,d), iv, INT(v/d), dv, v - iv*d, DROP(HSTACK(EXPAND("",,MATCH(A3, $E$2:$R$2,0), ""), (s>d-dv)*1 + iv),,1) )
Dynamic array formulas.