Replicating numbers instead of cascade

[labart]

I'm in the process of setting up a model based on a simple p&L centre. The idea is to model out one centre (something like a franchise store) and each time we open another store it starts at the beginning of the model. I could do a cascade (as illustrated below) but that is not very elegant and rather unwieldy as I'm looking on adding quite a few P&L lines.

I was hoping that there was some sort of array that I could use?


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]year 1[/TD]
[TD]year 2[/TD]
[TD]year 3[/TD]
[TD]year 4[/TD]
[TD]year 5[/TD]
[TD]year 6[/TD]
[TD]year...[/TD]
[/TR]
[TR]
[TD]Profits[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]17[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]27[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Opens[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Centre 1[/TD]
[TD]year 1[/TD]
[TD]5[/TD]
[TD]17[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]27[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Centre 2[/TD]
[TD]year 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]17[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Centre 3[/TD]
[TD]year5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Centre 4[/TD]
[TD]year6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Centre ...99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any ideas?

 

[labart]

Thanks Gaz_Chops,

Not quite. Here is an example of what I'm after (using HTMLMaker, again thanks for the recommendation):

The highlighted area at the bottom is what I'm trying to replicate without having to do multiple cascades of summations.

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1	Master Store P&L
2	year			1	2	3	4	5	6	7	8
3	Sales			5	6	7	8	9	10	11	12
4	Costs			-3	-3	-3	-3	-3	-3	-3	-3
5	GM			2	3	4	5	6	7	8	9
6	Operating Costs			-5	-5	-5	-5	-5	-5	-5	-5
7	EBITDA			-3	-2	-1	0	1	2	3	4
8
23	Store opening "Key"
24	Store	year Start
25	1	1		1	1	1	1	1	1	1	1
26	2	1		1	1	1	1	1	1	1	1
27	3	3		0	0	1	1	1	1	1	1
28	4	4		0	0	0	1	1	1	1	1
29	5	5		0	0	0	0	1	1	1	1
30	6	5		0	0	0	0	1	1	1	1
31	7	8		0	0	0	0	0	0	0	1
32
33	Consolidated Stores P&Ls
34	Sales			10	12	19	27	41	47	58	65
35	Costs			-6	-6	-9	-12	-18	-18	-21	-21
36	GM			4	6	10	15	23	29	37	44
37	Operating Costs			-10	-10	-15	-20	-30	-30	-35	-35
38	EBITDA			-6	-4	-5	-5	-7	-1	2	9
Sheet1

 

[gaz_chops]

Are your consolidated numbers correct?

[TABLE="width: 715"]
<tbody>[TR]
[TD="colspan: 2"]Master Store P&L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]year[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Store opening "Key"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store[/TD]
[TD]year Start[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Consolidated Stores P&Ls[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]-12[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-21[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-35[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[labart]

I believe so.

Here is an example of the sales line using the cascade method:

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K
1	Master Store P&L
2	year			1	2	3	4	5	6	7	8
3	Sales			5	6	7	8	9	10	11	12
4	Costs			-3	-3	-3	-3	-3	-3	-3	-3
5	GM			2	3	4	5	6	7	8	9
6	Operating Costs			-5	-5	-5	-5	-5	-5	-5	-5
7	EBITDA			-3	-2	-1	0	1	2	3	4
8
9
10
11
12	Cascade Trial
13	Store	year Start
14	1	1		5	6	7	8	9	10	11	12
15	2	1		5	6	7	8	9	10	11	12
16	3	3		0	0	5	6	7	8	9	10
17	4	4		0	0	0	5	6	7	8	9
18	5	5		0	0	0	0	5	6	7	8
19	6	5		0	0	0	0	5	6	7	8
20	7	7		0	0	0	0	0	0	5	6
21
22		Total		10	12	19	27	41	47	58	65
Sheet1

Cell Formulas
Range	Formula
D14	=OFFSET($D$3,,IF(D$2<$B14,-1,D$2-$B14))

 

[gaz_chops]

OK somehow i had a different year start for store 7!

Try
In cell D25 copy down & across
=IF($B25<=D$2,1+C25,0)

In cell D34 copy down & across

=SUMPRODUCT((D$25:D$31=$D$2:$K$2)*($D3:$K3))

[/FONT][TABLE="width: 715"]
<colgroup><col span="11"></colgroup><tbody>[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Store opening "Key"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store[/TD]
[TD]year Start[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Consolidated Stores P&Ls[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]-12[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-35[/TD]
[TD="align: right"]-35[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=Lucida Grande]

 

[labart]

Gz_Chops,

Sorry - my bad in year 7. I had different numbers in my cascade vs. the Key.

Either way, as Archimedes proclaimed on discovering how to measure volume while submerging himself in a bath: EUREKA!

Thank you ever so much for your assistance. This works perfectly. And that, without the need for an array. Perfect.


Now, I'll have to figure out how the equation works...

Thanks again

 

[gaz_chops]

You're welcome, glad we got there before the football starts

This is an array formula (enter with Ctrl, Shift Enter), it does away with data in the key table.

=SUMPRODUCT((IF($B$25:$B$31<=D$2,COLUMN(A1)-$B$25:$B$31+1,0)=$D$2:$K$2)*($D3:$K3))

[TABLE="width: 715"]
<tbody>[TR]
[TD="colspan: 2"]Master Store P&L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]year[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Store opening "Key"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store[/TD]
[TD]year Start[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Consolidated Stores P&Ls[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"]-12[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-18[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"]-21[/TD]
[/TR]
[TR]
[TD]GM[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]44[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Operating Costs[/TD]
[TD][/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"]-35[/TD]
[TD="align: right"]-35[/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

The formula works by matching the number in D25:D31 with the year in row 2, then multiplies each 1 by the value in Rows 3-7.

 

[labart]

Very interesting.

Would there be any way of making this into a 3D array as follows. Big stores and small stores. Perhaps with an indirect in the formula?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Type[/TD]
[TD]year entry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Perhaps by having the model stores in identical tabs called store1 and store2. Then using indirect?

 

[gaz_chops]

I don't follow what you mean!

 

[labart]

I don't follow what you mean!

All right here is the same example as before. I have put in a cascade again to illustrate what I'm after. The only difference from the previous example is that I would like to sum take the P&L figure from the different tabs depending on whether the store is a big store or a small store.
Excel 2012

	A	B	C	D	E	F	G	H	I	J	K
Sales

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Year[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]Store[/TD]
[TD="align: right"]Model[/TD]
[TD="align: right"]Year[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Small[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]Big[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Big[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]Big[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]Small[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]Small[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]Small[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]Big[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]76[/TD]

</tbody>

Key​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=OFFSET(INDIRECT("Store"&$B4&"!$D$3"),,IF(E$2<$C4,-1,E$2-$C4))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2012

	A	B	C	D	E	F	G	H	I	J
year
Sales
Costs
GM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]23[/TD]

</tbody>

StoreBig​

Excel 2012

	A	B	C	D	E	F	G	H	I	J
year
Sales
Costs
GM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]

</tbody>

StoreSmall​

 

[gaz_chops]

Which formula did you go with? The 1st one or the array?