Average sales for the previous 3 months for each product

[paun_shotts]

Hi,

So I am using Excel 2013, and I have a workbook that contains 6 products, we then have our sales for each month, dating back to July 2020.
In column D, I want to work out the average of the last 3 full months, and I want this to change as time goes on, so that we are always averaging the last 3 months, at any given point in time.
I would be very grateful if someone could help me in creating this formula. I have inserted the minisheet below.

PFM Experiment averages.xlsx
	A	B	C	D	E	F	G	H		J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA
1	Product Category			AVG Last 3 MTHS	ALL OH	NON 01	PO	BO		Jul-20	Aug-20	Sep-20	Q1	Oct-20	Nov-20	Dec-20	Q2	Jan-21	Feb-21	Mar-21	Q3	Apr-21	May-21	Jun-21	Q4	YTD	Jul-21	Aug-21	Sep-21	Q1	Oct-21	Nov-21	Dec-21	Q2	Jan-22	Feb-22	Mar-22	Q3	Apr-22	May-22	Jun-22	Q4	YTD	5%	Jul-22	Aug-22	Sep-22	Q1	Oct-22	Nov-22	Dec-22	Q2	Jan-23	Feb-23	Mar-23	Q3	Apr-23	May-23	Jun-23	Q4	YTD	9%	Jul-23	Aug-23	Sep-23	Q1	Oct-23	Nov-23	Dec-23	Q2	Jan-24	Feb-24	Mar-24	Q3	Apr-24	May-24	Jun-24	Q4	YTD
2	9000001		Product # 1		19	6	3	0		7	1	2	10	2	2	4	8	2	4	6	12	6	6	3	15	45	3	6	5	14	1	10	5	16	0	6	8	14	1	6	5	12	56	24%	3	4	5	12	3	7	4	14	3	7	5	15	1	10	9	20	61	9%	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
3	9000002		Product # 2		27	21	36	3		16	14	15	45	16	22	22	60	7	9	17	33	13	27	12	52	190	15	13	11	39	14	21	4	39	10	10	23	43	7	19	9	35	156	-18%	8	9	8	25	6	15	2	23	9	11	10	30	7	17	22	46	124	-21%	20	0	0	20	0	0	0	0	0	0	0	0	0	0	0	0	20
4	9000003		Product # 3		31	27	20	5		20	11	10	41	18	11	23	52	0	8	12	20	12	11	14	37	150	9	11	4	24	10	11	7	28	3	6	21	30	11	18	19	48	130	-13%	3	12	10	25	0	12	7	19	8	4	9	21	12	12	14	38	103	-21%	6	0	0	6	0	0	0	0	0	0	0	0	0	0	0	0	6
5	9000004		Product # 4		59	30	19	2		10	4	2	16	16	28	9	53	8	8	10	26	12	16	24	52	147	14	9	18	41	14	18	21	53	12	10	21	43	17	19	30	66	203	38%	16	29	14	59	19	16	29	64	11	23	19	53	29	9	15	53	229	13%	13	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	13
6	9000005		Product # 5		135	80	108	7		51	34	45	130	44	46	33	123	23	41	59	123	51	56	77	184	560	40	63	57	160	64	52	49	165	38	41	88	167	40	43	60	143	635	13%	42	68	52	162	57	77	73	207	37	52	92	181	39	62	87	188	738	16%	36	0	0	36	0	0	0	0	0	0	0	0	0	0	0	0	36
7	9000006		Product # 6		92	76	100	2		43	38	34	115	38	68	28	134	38	42	43	123	36	32	50	118	490	28	41	37	106	47	44	33	124	21	53	50	124	23	44	64	131	485	-1%	28	39	43	110	50	56	47	153	27	41	53	121	53	50	81	184	568	17%	36	0	0	36	0	0	0	0	0	0	0	0	0	0	0	0	36
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BW1:BY1	Expression	=ROW(BW1)=HighlightRow	text	NO
BS1:BU1	Expression	=ROW(BS1)=HighlightRow	text	NO
BO1:BQ1	Expression	=ROW(BO1)=HighlightRow	text	NO
BK1:BM1	Expression	=ROW(BK1)=HighlightRow	text	NO
BE1:BG1	Expression	=ROW(BE1)=HighlightRow	text	NO
BA1:BC1	Expression	=ROW(BA1)=HighlightRow	text	NO
AW1:AY1	Expression	=ROW(AW1)=HighlightRow	text	NO
AT1:AU1	Expression	=ROW(AT1)=HighlightRow	text	NO
AS1	Expression	=ROW(AS1)=HighlightRow	text	NO
U1	Expression	=ROW(U1)=HighlightRow	text	NO
Q1	Expression	=ROW(Q1)=HighlightRow	text	NO
M1	Expression	=ROW(M1)=HighlightRow	text	NO
BN1,J1:L1,N1:P1,R1:T1,V1:X1,BR1,BV1	Expression	=ROW(J1)=HighlightRow	text	NO
BZ1	Expression	=ROW(BZ1)=HighlightRow	text	NO
D2:D7	Expression	=ROW(D2)=HighlightRow	text	NO
BV2:BV7	Expression	=ROW(BV2)=HighlightRow	text	NO
BR2:BR7	Expression	=ROW(BR2)=HighlightRow	text	NO
BN2:BN7	Expression	=ROW(BN2)=HighlightRow	text	NO
BW2:BY7	Expression	=ROW(BW2)=HighlightRow	text	NO
BS2:BU7	Expression	=ROW(BS2)=HighlightRow	text	NO
BO2:BQ7	Expression	=ROW(BO2)=HighlightRow	text	NO
BL2:BM7	Expression	=ROW(BL2)=HighlightRow	text	NO
BK2:BK7	Expression	=ROW(BK2)=HighlightRow	text	NO
BZ2:BZ7	Expression	=ROW(BZ2)=HighlightRow	text	NO
BJ1	Cell Value	<=0	text	NO
BJ1	Cell Value	>0	text	NO
BJ1	Expression	=ROW(BJ1)=HighlightRow	text	NO
AR2:AR7	Cell Value	<=0	text	NO
AR2:AR7	Cell Value	>0	text	NO
AR2:AR7	Expression	=ROW(AR2)=HighlightRow	text	NO
BJ2:BJ7	Cell Value	<=0	text	NO
BJ2:BJ7	Cell Value	>0	text	NO
BJ2:BJ7	Expression	=ROW(BJ2)=HighlightRow	text	NO
X2:X7	Expression	=ROW(X2)=HighlightRow	text	NO
W2:W7	Expression	=ROW(W2)=HighlightRow	text	NO
V2:V7	Expression	=ROW(V2)=HighlightRow	text	NO
T2:T7	Expression	=ROW(T2)=HighlightRow	text	NO
S2:S7	Expression	=ROW(S2)=HighlightRow	text	NO
R2:R7	Expression	=ROW(R2)=HighlightRow	text	NO
P2:P7	Expression	=ROW(P2)=HighlightRow	text	NO
O2:O7	Expression	=ROW(O2)=HighlightRow	text	NO
N2:N7	Expression	=ROW(N2)=HighlightRow	text	NO
L2:L7	Expression	=ROW(L2)=HighlightRow	text	NO
K2:K7	Expression	=ROW(K2)=HighlightRow	text	NO
J2:J7	Expression	=ROW(J2)=HighlightRow	text	NO
M2:M7,Q2:Q7,U2:U7	Expression	=ROW(M2)=HighlightRow	text	NO
AO2:AO7	Expression	=ROW(AO2)=HighlightRow	text	NO
AN2:AN7	Expression	=ROW(AN2)=HighlightRow	text	NO
AM2:AM7	Expression	=ROW(AM2)=HighlightRow	text	NO
AK2:AK7	Expression	=ROW(AK2)=HighlightRow	text	NO
AJ2:AJ7	Expression	=ROW(AJ2)=HighlightRow	text	NO
AI2:AI7	Expression	=ROW(AI2)=HighlightRow	text	NO
AG2:AG7	Expression	=ROW(AG2)=HighlightRow	text	NO
AF2:AF7	Expression	=ROW(AF2)=HighlightRow	text	NO
AE2:AE7	Expression	=ROW(AE2)=HighlightRow	text	NO
AC2:AC7	Expression	=ROW(AC2)=HighlightRow	text	NO
AB2:AB7	Expression	=ROW(AB2)=HighlightRow	text	NO
AA2:AA7	Expression	=ROW(AA2)=HighlightRow	text	NO
AV1,AZ1,BD1	Expression	=ROW(AV1)=HighlightRow	text	NO
BD2:BD7	Expression	=ROW(BD2)=HighlightRow	text	NO
AZ2:AZ7	Expression	=ROW(AZ2)=HighlightRow	text	NO
AV2:AV7	Expression	=ROW(AV2)=HighlightRow	text	NO
BE2:BG7	Expression	=ROW(BE2)=HighlightRow	text	NO
BA2:BC7	Expression	=ROW(BA2)=HighlightRow	text	NO
AW2:AY7	Expression	=ROW(AW2)=HighlightRow	text	NO
AT2:AU7	Expression	=ROW(AT2)=HighlightRow	text	NO
AS2:AS7	Expression	=ROW(AS2)=HighlightRow	text	NO
AR1	Cell Value	<=0	text	NO
AR1	Cell Value	>0	text	NO
AR1	Expression	=ROW(AR1)=HighlightRow	text	NO
BH2:BH7	Expression	=ROW(BH2)=HighlightRow	text	NO
BH1	Expression	=ROW(BH1)=HighlightRow	text	NO
AQ1,AQ4,AQ7	Expression	=ROW(AQ1)=HighlightRow	text	NO
AQ2:AQ3,AQ5:AQ6	Expression	=ROW(AQ2)=HighlightRow	text	NO
Z1:Z7	Expression	=ROW(Z1)=HighlightRow	text	NO
H2:H7	Cell Value	>0	text	NO
Y1:Y7,AA1:AP1,AP2:AP7,AD2:AD7,AH2:AH7,AL2:AL7,I1:I7,A1:H1,E2:H7,A2:C7	Expression	=ROW(A1)=HighlightRow	text	NO

 

[bebo021999]

With this words, " the last 3 full months", do you mean, if today is 28-Jul-2023, you want to say it is from 1-Apr-2023 to 30-6/2023?
If yes, try:

=AVERAGEIFS($J2:$BY2,$J$1:$BY$1,">=" & EOMONTH(TODAY(),-4)+1,$J$1:$BY$1,"<=" & EOMONTH(TODAY(),-2)+1)

Note: if you want to says, last quarter, it requires different formula.

 

[paun_shotts]

With this words, " the last 3 full months", do you mean, if today is 28-Jul-2023, you want to say it is from 1-Apr-2023 to 30-6/2023?
If yes, try:

=AVERAGEIFS($J2:$BY2,$J$1:$BY$1,">=" & EOMONTH(TODAY(),-4)+1,$J$1:$BY$1,"<=" & EOMONTH(TODAY(),-2)+1)

Note: if you want to says, last quarter, it requires different formula.

Thank you! Perfect