How to dynamically update formula in columns

[eol666]

Per the attached spreadsheet I wish to be able to add a new column (Week 6) and have the formulas in H5, I5, J5 automatically reflect the new column.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Week 1	Week 2	Week 3	Week 4	Week 5	Week gain / loss	New Max	Old max	Gain / Loss
2	Investment 1	500	635	400	630	925	295
3	Investment 2	200	325	600	100	800	700
4	Investment 3	1000	1000	575	1200	325	-875
5	Total Portfolio	1700	1960	1575	1930	2050	120	2050	1960	90
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=INDEX(A:F,ROW(),COLUMN()-1)-INDEX(A:F,ROW(),COLUMN()-2)
B5:F5	B5	=SUM(B2:B4)
H5	H5	=MAX(B5:F5)
I5	I5	=MAX(B5:E5)
J5	J5	=SUM(F5-I5)

 

[JEC]

Use real Excel Table. Everything works dynamically in tables

 

[eol666]

I'm sorry I have no idea what a real Excel Table is. If you mean convert my spreadsheet to a table I tried that with no success. So I'm not understanding the solution.

 

[eol666]

I had the formulas in column G updated to reflect the array correctly.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Week 1	Week 2	Week 3	Week 4	Week 5	Week gain / loss	New Max	Old max	Gain / Loss
2	Investment 1	500	635	400	630	925	295
3	Investment 2	200	325	600	100	800	700
4	Investment 3	1000	1000	575	1200	325	-875
5	Total Portfolio	1700	1960	1575	1930	2050	120	2050	1960	90
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=INDEX(A:G,ROW(),COLUMN()-1)-INDEX(A:G,ROW(),COLUMN()-2)
B5:F5	B5	=SUM(B2:B4)
H5	H5	=MAX(B5:F5)
I5	I5	=MAX(B5:E5)
J5	J5	=SUM(F5-I5)