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]

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

I've gone with the first one, but am happy to switch if needed.

Regards

 

[gaz_chops]

OK, this i the best I can come up with for now. Basically it is 2 formulas 1 for Big, 1 for Small, added together, not sure if it is exactly what you want?

=SUMPRODUCT((IF($C$25:$C$32<=D$2,COLUMN(A1)-$C$25:$C$32+1,0)=StoreSmall!$D$2:$K$2)*($B$25:$B$32="Small")*(StoreSmall!$D3:$K3))+SUMPRODUCT((IF($C$25:$C$32<=D$2,COLUMN(A1)-$C$25:$C$32+1,0)=StoreBig!$D$2:$K$2)*($B$25:$B$32="Big")*(StoreBig!$D3:$K3))

 

[labart]

That works. I had to Shift-Ctrl-Enter to make it an array.

Fantastic.

Thanks!

OK, this i the best I can come up with for now. Basically it is 2 formulas 1 for Big, 1 for Small, added together, not sure if it is exactly what you want?

=SUMPRODUCT((IF($C$25:$C$32<=D$2,COLUMN(A1)-$C$25:$C$32+1,0)=StoreSmall!$D$2:$K$2)*($B$25:$B$32="Small")*(StoreSmall!$D3:$K3))+SUMPRODUCT((IF($C$25:$C$32<=D$2,COLUMN(A1)-$C$25:$C$32+1,0)=StoreBig!$D$2:$K$2)*($B$25:$B$32="Big")*(StoreBig!$D3:$K3))

 

[gaz_chops]

You're welcome, sorry forgot to say it was an array formula. If I get a better solution I'll post it. Or maybe someone else will chip in.