"Cascading" Excel Formula

[Guims]

Hi all,

I have two tables similar in size and I would like to divide part of the first table by the equivalent part of the second table. I'm looking for a single formula that I can duplicate for the whole series. In the example below, I compare 3x3 cells in the first table by the equivalent 3x3 in the second table (but it could need to be 4x4, 5x5 etc.). As I would expand the formula to the right, I want the formula to "offset" the 3x3 cells (or more, depending on the imput) one down and one to the right :

	85,5​	84,6​	66,6​	77,4​	75,6​	86,4​
	0​	855​	846​	666​	774​	756​
	0​	0​	1026​	1015,2​	799,2​	928,8​
	0​	0​	0​	513​	507,6​	399,6​
	0​	0​	0​	0​	1710​	1692​
	0​	0​	0​	0​	0​	3420​
	100​	100​	100​	100​	100​	100​
	0​	1000​	1000​	1000​	1000​	1000​
	0​	0​	1200​	1200​	1200​	1200​
	0​	0​	0​	600​	600​	600​
	0​	0​	0​	0​	2000​	2000​
	0​	0​	0​	0​	0​	4000​
Manual formula:	=C2/C9	=SUM(C2:D3)/SUM(C9:D10)	=SUM(C2:E4)/SUM(C9:E11)	=SUM(D3:F5)/SUM(D10:F12)	=SUM(E4:G6)/SUM(E11:G13)	=SUM(F5:H7)/SUM(F12:H14)
Expandable formula:	?	?	?	?	?	?

I hope this is clear enough.

Thanks in advance,
G.

 

[Guims]

Hi all, just rephrasing the above as I realise that it may not be very clear:

I am looking for an Excel formula that would allow me to divide part of a table by an equivalent-sized part of another table. I need the formula to be unique and extendable to the right. The first result of the formula would only be the first cell of table 1 divided by the first cell of table 2 (as in cell C16), and, as the formula is expanded to the right, the size of the sectors being divided would increase until they get to an aimed fixed size (in this example, 3x3). Once the aimed size is achieved, the part of the table being divided would go one down, and one to the right as the formula would be further extended to the right. I've screenshoted each step below using manual formulas and would like to find a single formula that would do the job (because the tables I am working with are much bigger, so I can't manually write each formula) :

Thanks,
G.

 

[vladimiratanasiu]

Hi all, just rephrasing the above as I realise that it may not be very clear:

I am looking for an Excel formula that would allow me to divide part of a table by an equivalent-sized part of another table. I need the formula to be unique and extendable to the right. The first result of the formula would only be the first cell of table 1 divided by the first cell of table 2 (as in cell C16), and, as the formula is expanded to the right, the size of the sectors being divided would increase until they get to an aimed fixed size (in this example, 3x3). Once the aimed size is achieved, the part of the table being divided would go one down, and one to the right as the formula would be further extended to the right. I've screenshoted each step below using manual formulas and would like to find a single formula that would do the job (because the tables I am working with are much bigger, so I can't manually write each formula) :

Thanks,
G.

See Create a relationship between tables in Excel

 

[vladimiratanasiu]

Hi all, just rephrasing the above as I realise that it may not be very clear:

I am looking for an Excel formula that would allow me to divide part of a table by an equivalent-sized part of another table. I need the formula to be unique and extendable to the right. The first result of the formula would only be the first cell of table 1 divided by the first cell of table 2 (as in cell C16), and, as the formula is expanded to the right, the size of the sectors being divided would increase until they get to an aimed fixed size (in this example, 3x3). Once the aimed size is achieved, the part of the table being divided would go one down, and one to the right as the formula would be further extended to the right. I've screenshoted each step below using manual formulas and would like to find a single formula that would do the job (because the tables I am working with are much bigger, so I can't manually write each formula) :

Thanks,
G.

Steps to create relationships between multiple tables using Data Model in Excel

Excel is a powerful tool for data analysis and automation after processing large data sets. You can spend considerable time analyzing tons of data using VLOOKUP, INDEX-MATCH,

tipsmake.com

 

[Guims]

Hi,

Thank you for the link. Unfortunately, this does not seem to solve my issue. I'm looking for a formula to put below both graphs that divides one area table by the other and where, when I expand the formula to one cell to the right, it divides the equivalent areas one to the right and one down. I think the challenging part of the formula is to get the areas to go down one as the formula is expanded to the right. The fact that it needs to appear as a single formula line below the graphs is important since additional work is done on it afterwards.

Thanks a lot,
G.