Look up values of month from previous year

[Forestq]

Hi,

I need to write a function, which find value from month from previous year:

Below is example of my data (I have year starting from 1992 - but it should not have meaning)

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Quater[/TD]
[TD]RepQua[/TD]
[TD]ID[/TD]
[TD]Value[/TD]
[TD]PrevValue[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Jan[/TD]
[TD]Q1[/TD]
[TD]FY15Q1[/TD]
[TD]A1B[/TD]
[TD]10[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Apr[/TD]
[TD]Q2[/TD]
[TD]FY15Q2[/TD]
[TD]B2C[/TD]
[TD]20%[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Apr[/TD]
[TD]Q2[/TD]
[TD]FY15Q2[/TD]
[TD]C11A[/TD]
[TD]3%[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]May[/TD]
[TD]Q3[/TD]
[TD]FY15Q3[/TD]
[TD]H76[/TD]
[TD]4[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]June[/TD]
[TD]Q3[/TD]
[TD]FY15Q3[/TD]
[TD]J87Q[/TD]
[TD]62[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Jan[/TD]
[TD]Q1[/TD]
[TD]FY16Q1[/TD]
[TD]A1B[/TD]
[TD]20[/TD]
[TD]should be 10[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Apr[/TD]
[TD]Q2[/TD]
[TD]FY15Q2[/TD]
[TD]B2C[/TD]
[TD]5%[/TD]
[TD]should be 20%[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Apr[/TD]
[TD]Q2[/TD]
[TD]FY15Q2[/TD]
[TD]C11A[/TD]
[TD]7[/TD]
[TD]should be 3%[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]May[/TD]
[TD]Q3[/TD]
[TD]FY15Q3[/TD]
[TD]H76[/TD]
[TD]54[/TD]
[TD]should be 4[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]June[/TD]
[TD]Q3[/TD]
[TD]FY15Q3[/TD]
[TD]J87Q[/TD]
[TD]70[/TD]
[TD]should be 62[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance.

 

[AlanY]

are the values 10, 20% etc are all percentages or mixed with numbers?

 

[DiscoPistol]

Hi,

If you assume that the Names ranges are as suggested this seems to work for me if you enter as an array (Control/Shift/Enter)

=SUM((Year=C11-1)*(Month=D11)*(ID = G11)*(Value))

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E	F	G
1	Year	Month	Quater	RepQua	ID	Value	PrevValue
2	2015	Jan	Q1	FY15Q1	A1B	10	-
3	2015	Apr	Q2	FY15Q2	B2C	20%	-
4	2015	Apr	Q2	FY15Q2	C11A	3%	-
5	2015	May	Q3	FY15Q3	H76	4	-
6	2015	June	Q3	FY15Q3	J87Q	62	-
7	2016	Jan	Q1	FY16Q1	A1B	20	10
8	2016	Apr	Q2	FY15Q2	B2C	5%	0.2
9	2016	Apr	Q2	FY15Q2	C11A	7	0.03
10	2016	May	Q3	FY15Q3	H76	54	4
11	2016	June	Q3	FY15Q3	J87Q	70	62
Sheet2

Cell Formulas
Range	Formula
G7	=SUMPRODUCT(--($A$2:$A$11=(A7-1)),--($B$2:$B$11=B7),--($C$2:$C$11=C7),--($E$2:$E$11=E7),$F$2:$F$11)

and conditional format column with cell value <1 with percentage

Excel 2012
	A	B	C	D	E	F	G
1	Year	Month	Quater	RepQua	ID	Value	PrevValue
2	2015	Jan	Q1	FY15Q1	A1B	10	-
3	2015	Apr	Q2	FY15Q2	B2C	20%	-
4	2015	Apr	Q2	FY15Q2	C11A	3%	-
5	2015	May	Q3	FY15Q3	H76	4	-
6	2015	June	Q3	FY15Q3	J87Q	62	-
7	2016	Jan	Q1	FY16Q1	A1B	20	10
8	2016	Apr	Q2	FY15Q2	B2C	5%	20%
9	2016	Apr	Q2	FY15Q2	C11A	7	3%
10	2016	May	Q3	FY15Q3	H76	54	4
11	2016	June	Q3	FY15Q3	J87Q	70	62
Sheet2

 

[Forestq]

thanks AlanY!

it's possible to modify:

- Type -> N it's number and column "Value N" has only number
- Type -> P it's percentage and column "Value P" has only percetage

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]...id[/TD]
[TD]Type[/TD]
[TD]Value N[/TD]
[TD]Value P[/TD]
[TD]PrevValue[/TD]
[/TR]
[TR]
[TD]A1B[/TD]
[TD]N[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]looking value 10[/TD]
[/TR]
[TR]
[TD]B2C[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]23%[/TD]
[TD]looking value 20%[/TD]
[/TR]
[TR]
[TD]C11A[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]5%[/TD]
[TD]looking value 3%[/TD]
[/TR]
[TR]
[TD]H75[/TD]
[TD]N[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]looking value 4[/TD]
[/TR]
[TR]
[TD]8UP[/TD]
[TD]N[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]looking value 105[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

this LONG formula that I'm sure someone can improve it should work without to seperate it into 2 columns and CF;

Excel 2012
	A	B	C	D	E	F	G
1	Year	Month	Quater	RepQua	ID	Value
2	2015	Jan	Q1	FY15Q1	A1B	10
3	2015	Apr	Q2	FY15Q2	B2C	20%
4	2015	Apr	Q2	FY15Q2	C11A	3%
5	2015	May	Q3	FY15Q3	H76	4
6	2015	June	Q3	FY15Q3	J87Q	62
7	2016	Jan	Q1	FY16Q1	A1B	20	10
8	2016	Apr	Q2	FY15Q2	B2C	5%	20%
9	2016	Apr	Q2	FY15Q2	C11A	7	3%
10	2016	May	Q3	FY15Q3	H76	54	4
11	2016	June	Q3	FY15Q3	J87Q	70	62
Sheet2

Cell Formulas
Range	Formula
G7	=IF(SUMPRODUCT(--($A$2:$A$11=(A7-1)),--($B$2:$B$11=B7),--($C$2:$C$11=C7),--($E$2:$E$11=E7),$F$2:$F$11)<1,TEXT(SUMPRODUCT(--($A$2:$A$11=(A7-1)),--($B$2:$B$11=B7),--($C$2:$C$11=C7),--($E$2:$E$11=E7),$F$2:$F$11),0&"%"),SUMPRODUCT(--($A$2:$A$11=(A7-1)),--($B$2:$B$11=B7),--($C$2:$C$11=C7),--($E$2:$E$11=E7),$F$2:$F$11))

 

[AlanY]

which is the same as an extra column but more readable

Excel 2012
	A	B	C	D	E	F	G	H
1	Year	Month	Quater	RepQua	ID	Value
2	2015	Jan	Q1	FY15Q1	A1B	10
3	2015	Apr	Q2	FY15Q2	B2C	20%
4	2015	Apr	Q2	FY15Q2	C11A	3%
5	2015	May	Q3	FY15Q3	H76	4
6	2015	June	Q3	FY15Q3	J87Q	62
7	2016	Jan	Q1	FY16Q1	A1B	20	10	10
8	2016	Apr	Q2	FY15Q2	B2C	5%	0.2	20%
9	2016	Apr	Q2	FY15Q2	C11A	7	0.03	3%
10	2016	May	Q3	FY15Q3	H76	54	4	4
11	2016	June	Q3	FY15Q3	J87Q	70	62	62
Sheet2

Cell Formulas
Range	Formula
G7	=SUMPRODUCT(--($A$2:$A$11=(A7-1)),--($B$2:$B$11=B7),--($C$2:$C$11=C7),--($E$2:$E$11=E7),$F$2:$F$11)
H7	=IF(G7<1,TEXT(G7,0&"%"),G7)

 

[Forestq]

I did exaclty the same way.

Thanks AlanY for quick help!

 

[AlanY]

you're welcome

 

[Forestq]

also,

we can do like below:

=if(F2="N",
IF(SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$G$2:$G$16)<1,TEXT(SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$G$2:$G$16),0&"%"),SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$G$2:$G$16)),


IF(SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$H$2:$H$16)<1,TEXT(SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$H$2:$H$16),0&"%"),SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$H$2:$H$16)))

or

=IF(F2="N",SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$G$2:$G$16),SUMPRODUCT(--($A$2:$A$16=(A2-1)),--($B$2:$B$16=B2),--($C$2:$C$16=C2),--($E$2:$E$16=E2),$H$2:$H$16))