Cumulative Formula Issue

[Mr Sloth]

Hi All,

Each week I update sales values (See columns D-H) which I want to compare against budget (see variance column in L). However, each week I have to manually change the formula to pick up the latest sales number. Is there a formula I can use to automatically pick up the latest week?

I have attached an example.

Many thanks in advance.

Book1
	D	E	F	G	H	I	J	K	L
7	01/07/2022	08/07/2022	15/07/2022	22/07/2022	29/07/2022		Budget		Var
8	100,000	150,000	200,000				120,000		-20,000
9	50,000						65,000		-15,000
10	20,000						60,000		-40,000
Sheet1

Cell Formulas
Range	Formula
L8:L10	L8	=D8-J8

 

[alansidman]

If you normalize your data table, then you could do this with a pivot table and it would update with each entry.

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Product	Sales$	Date	Budget			Column Labels
2	A	100000	7/1/2022	120000			7/1/2022			7/8/2022			7/15/2022
3	B	50000	7/1/2022	65000		Row Labels	Sum of Sales$	Sum of Budget	Sum of Variance	Sum of Sales$	Sum of Budget	Sum of Variance	Sum of Sales$	Sum of Budget	Sum of Variance
4	C	20000	7/1/2022	60000		A	100000	120000	20000	150000	120000	-30000	200000	120000	-80000
5	A	150000	7/8/2022	120000		B	50000	65000	15000			0			0
6	A	200000	7/15/2022	120000		C	20000	60000	40000			0			0
7
Sheet1

 

[Mr Sloth]

If you normalize your data table, then you could do this with a pivot table and it would update with each entry.

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Product	Sales$	Date	Budget			Column Labels
2	A	100000	7/1/2022	120000			7/1/2022			7/8/2022			7/15/2022
3	B	50000	7/1/2022	65000		Row Labels	Sum of Sales$	Sum of Budget	Sum of Variance	Sum of Sales$	Sum of Budget	Sum of Variance	Sum of Sales$	Sum of Budget	Sum of Variance
4	C	20000	7/1/2022	60000		A	100000	120000	20000	150000	120000	-30000	200000	120000	-80000
5	A	150000	7/8/2022	120000		B	50000	65000	15000			0			0
6	A	200000	7/15/2022	120000		C	20000	60000	40000			0			0
7
Sheet1

Thanks but for this particular report I only want one variance, which is for the latest week.

 

[Sufiyan97]

Try

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5
6
7				7/1/2022	7/8/2022	7/15/2022	7/22/2022	7/29/2022		Budget		Var
8				100000	150000	200000				120000		30000
9				50000						65000		-65000
10				20000						60000		-60000
11
12
Sheet1

Cell Formulas
Range	Formula
L8:L10	L8	=INDEX(D8:H8,MATCH(TODAY(),$D$7:$H$7,1))-J8

 

[alansidman]

I understand your needs, but I would still recommend that you normalize your data and then you can easily analyze your data. With your current configuration, you will constantly adding columns and having to move your calculations.

 

[Mr Sloth]

I understand your needs, but I would still recommend that you normalize your data and then you can easily analyze your data. With your current configuration, you will constantly adding columns and having to move your calculations.

Thank you. I'll take this into consideration.

 

[Mr Sloth]

Try

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5
6
7				7/1/2022	7/8/2022	7/15/2022	7/22/2022	7/29/2022		Budget		Var
8				100000	150000	200000				120000		30000
9				50000						65000		-65000
10				20000						60000		-60000
11
12
Sheet1

Cell Formulas
Range	Formula
L8:L10	L8	=INDEX(D8:H8,MATCH(TODAY(),$D$7:$H$7,1))-J8

Thank you very much that has worked perfectly!!!

 

[Sufiyan97]

You're welcome!
Glad we could help.

 

[Peter_SSs]

Thank you very much that has worked perfectly!!!

If that does what you want then another option might be

22 07 13.xlsm
	D	E	F	G	H	I	J	K	L
7	01/07/2022	08/07/2022	15/07/2022	22/07/2022	29/07/2022		Budget		Var
8	100,000	150,000	200,000				120,000		30,000
9	50,000						65,000		-65,000
10	20,000						60,000		-60,000
Var

Cell Formulas
Range	Formula
L8:L10	L8	=LOOKUP(TODAY(),D$7:H8)-J8