Creating Continuous, Growing List

[mcgonma]

In column A I have a total number of items (e.g., A1=46.8, A2=43.4, A3=26.2, etc.).
In column B I have the total cost of all the items (e.g., B1=$4,201,063, B2=$1,841,198, B3=$1,069,026, etc.)
In column C I have the average cost of each item (i.e., C=B/A) (e.g., C1=$89,817, C2=42,457, C3=$40,802, etc.)

How can I set up column D so that I have the results of C1 replicated down by the same number of times indicated in A1, followed by the results of C2 replicated down by the number of times indicated in A2, followed by the results of C3 replicated down by the number of times indicated in A3, etc., etc. etc.?

 

[Eric W]

If the number in column A is not an integer, how do you decide how many times the C value should be replicated? For now, I just assumed all integers:

Book1
	A	B	C	D
1
2	4	4201063	1050266	1050266
3	5	1841198	368239.6	1050266
4	2	1069026	534513	1050266
5				1050266
6				368239.6
7				368239.6
8				368239.6
9				368239.6
10				368239.6
11				534513
12				534513
13
Sheet2

Cell Formulas
Range	Formula
D2:D12	D2	=INDEX(C2:C10,MATCH(SEQUENCE(SUM(A:A),,0),SUBTOTAL(9,OFFSET(A1,0,0,SEQUENCE(COUNT(A:A))))))
C2:C4	C2	=B2/A2
Dynamic array formulas.

This assumes your version of Excel has the SEQUENCE function. If not, please update your profile to show the version of Excel you're using. Also, the empty row on top is necessary.

 

[phillip87]

If the number in column A is not an integer, how do you decide how many times the C value should be replicated? For now, I just assumed all integers:

Book1
	A	B	C	D
1
2	4	4201063	1050266	1050266
3	5	1841198	368239.6	1050266
4	2	1069026	534513	1050266
5				1050266
6				368239.6
7				368239.6
8				368239.6
9				368239.6
10				368239.6
11				534513
12				534513
13
Sheet2

Cell Formulas
Range	Formula
D2:D12	D2	=INDEX(C2:C10,MATCH(SEQUENCE(SUM(A:A),,0),SUBTOTAL(9,OFFSET(A1,0,0,SEQUENCE(COUNT(A:A))))))
C2:C4	C2	=B2/A2
Dynamic array formulas.

This assumes your version of Excel has the SEQUENCE function. If not, please update your profile to show the version of Excel you're using. Also, the empty row on top is necessary.

This is great but how could it be made to instead list a number 1 - 100 depending on how many times it copies so in the above it would go 1 - 4 then 1 - 5 then 1 - 2

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H
1
2	4	4201063	1050266	1050266	1		1	1050266
3	5	1841198	368239.6	1050266	2		2	1050266
4	2	1069026	534513	1050266	3		3	1050266
5				1050266	4		4	1050266
6				368239.6	1		1	368239.6
7				368239.6	2		2	368239.6
8				368239.6	3		3	368239.6
9				368239.6	4		4	368239.6
10				368239.6	5		5	368239.6
11				534513	1		1	534513
12				534513	2		2	534513
13
Sheet2

Cell Formulas
Range	Formula
D2:D12	D2	=INDEX(C2:C10,MATCH(SEQUENCE(SUM(A:A),,0),SUBTOTAL(9,OFFSET(A1,0,0,SEQUENCE(COUNT(A:A))))))
E2:E12	E2	=LET(s,SEQUENCE(SUM(A:A)),t,SUBTOTAL(9,OFFSET(A1,0,0,SEQUENCE(COUNT(A:A)))),s-INDEX(t,MATCH(SEQUENCE(SUM(A:A),,0),t)))
G2:H12	G2	=LET(s,SEQUENCE(SUM(A:A)),t,SUBTOTAL(9,OFFSET(A1,0,0,SEQUENCE(COUNT(A:A)))),m,MATCH(s-1,t),CHOOSE({1,2},s-INDEX(t,m),INDEX(C2:C10,m)))
C2:C4	C2	=B2/A2
Dynamic array formulas.

If you have LET, then the G2 formula combines both the D and E formulas. If you don't have LET, the E2 formula would be:

=SEQUENCE(SUM(A:A))-INDEX(SUBTOTAL(9,OFFSET(A1,0,0,SEQUENCE(COUNT(A:A)))),MATCH(SEQUENCE(SUM(A:A),,0),SUBTOTAL(9,OFFSET(A1,0,0,SEQUENCE(COUNT(A:A))))))

 

[mcgonma]

If the number in column A is not an integer, how do you decide how many times the C value should be replicated? For now, I just assumed all integers:

Book1
	A	B	C	D
1
2	4	4201063	1050266	1050266
3	5	1841198	368239.6	1050266
4	2	1069026	534513	1050266
5				1050266
6				368239.6
7				368239.6
8				368239.6
9				368239.6
10				368239.6
11				534513
12				534513
13
Sheet2

Cell Formulas
Range	Formula
D2:D12	D2	=INDEX(C2:C10,MATCH(SEQUENCE(SUM(A:A),,0),SUBTOTAL(9,OFFSET(A1,0,0,SEQUENCE(COUNT(A:A))))))
C2:C4	C2	=B2/A2
Dynamic array formulas.

This assumes your version of Excel has the SEQUENCE function. If not, please update your profile to show the version of Excel you're using. Also, the empty row on top is necessary.

Erik...this solution is GORGEOUS!!!!! It works beautifully and is exactly what I was looking to accomplish!!!!

 

[Eric W]

Glad it works for you! Thanks for the feedback.

 

[lrobbo314]

Here is another way using Power Query.

PQ
	A	B	C	D	E
1	Times	Cost		ID	Avg
2	4	4,201,063		1	1,050,266
3	5	1,841,198		2	1,050,266
4	2	1,069,026		3	1,050,266
5				4	1,050,266
6				1	368,240
7				2	368,240
8				3	368,240
9				4	368,240
10				5	368,240
11				1	534,513
12				2	534,513
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ID = Table.AddColumn(Source, "ID", each {1..[Times]}),
    Avg = Table.AddColumn(ID, "Avg", each [Cost]/[Times]),
    Expand = Table.ExpandListColumn(Avg, "ID"),
    ROC = Table.SelectColumns(Expand,{"ID", "Avg"})
in
    ROC