Automatically add new Month and pick values automatically based on Month no and subtract from previous month

[hananak]

Hi All,

I am struggling to write a formula which will check the current month and then subtract from the current month the previous month.

Your help would be appreciated.

Formula Required.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		M5
3
4	Expenditure	M1	M2	M3	M4	M5	M6	M7	M8	M9	M10	M11	M12		Current Month	MoM Movement	Movement %
5	Expense 1	50	60	70	25	10	-	-	-	-	-	-	-		10	(15)	-150%
6	Expense 2	20	30	35	15	9	-	-	-	-	-	-	-		9	(6)	-67%
7	Expense 3	90	180	20	10	4	-	-	-	-	-	-	-		4	(6)	-150%
8
9
10
11		Currently the formula in column R picks values based on month chosen in cell E2
12
13		I want a formula in column T to check the what is the current month and subtract from it previous month.
14
15		For example if current month is M5 then subtract from M5 the M4 values, if current month is M6 then subtract M5 values from it and if current month is M1 then it should have value 0 as there is no movement.
Sheet2

Cell Formulas
Range	Formula
R5:R7	R5	=INDEX($E5:$P5,,MATCH($E$2,$E$4:$P$4,0))
S5:S7	S5	=+R5-H5
T5:T7	T5	=+IFERROR(S5/R5,"")

 

[SanjayGMusafir]

Check the green highlighted cells. Most of the things you had already done correctly. All you needed was a concept to improvise.

I'm giving you the same. Check this -

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2		M5	Start	May-2023
3
4	Expenditure	May-2023	Jun-2023	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	Feb-2024	Mar-2024	Apr-2024		Current Month	MoM Movement	Movement %
5	Expense 1	50	60	70	25	10	0	0	0	0	0	0	0		10	-15	-150%
6	Expense 2	20	30	35	15	9	0	0	0	0	0	0	0		9	-15	-67%
7	Expense 3	90	180	20	10	4	0	0	0	0	0	0	0		4	-15	-150%
Sheet1

Cell Formulas
Range	Formula
E4	E4	=EOMONTH($G$2,0)
F4	F4	=EOMONTH($G$2,1)
G4	G4	=EOMONTH($G$2,2)
H4	H4	=EOMONTH($G$2,3)
I4	I4	=EOMONTH($G$2,4)
J4	J4	=EOMONTH($G$2,5)
K4	K4	=EOMONTH($G$2,6)
L4	L4	=EOMONTH($G$2,7)
M4	M4	=EOMONTH($G$2,8)
N4	N4	=EOMONTH($G$2,9)
O4	O4	=EOMONTH($G$2,10)
P4	P4	=EOMONTH($G$2,11)
R5:R7	R5	=INDEX($E5:$P5,,MATCH(EOMONTH(TODAY(),0),$E$4:$P$4,0))
S5:S7	S5	=INDEX($E5:$P5,,MATCH(EOMONTH(TODAY(),0),$E$4:$P$4,0))-INDEX($E5:$P5,,MATCH(EOMONTH(TODAY(),-1),$E$4:$P$4,0))
T5	T5	=IFERROR(S5/R5,"")
T6:T7	T6	=+IFERROR(S6/R6,"")