Average a Range of Data by Finding a Starting Month

[seasea123]

Can you please help build a formula in column AA? We would like the formula to average the 3 months prior to the month of change. In this case the month of change is Oct 23, so it should average Sep 23, Aug 23 and Jul 23. We'd like the formula to be dynamic for different months of change.

 

[seasea123]

Can you please help build a formula in column AA? We would like the formula to average the 3 months prior to the month of change. In this case the month of change is Oct 23, so it should average Sep 23, Aug 23 and Jul 23. We'd like the formula to be dynamic for different months of change.

View attachment 103356

Column AA is the cell that's highlighted in yellow.

 

[Asbestos_Jen]

Try this:

2023-12-11.2.xlsx
	T	U	V	W	X	Y	Z	AA
1	Name	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Month of change	Baseline (3 month avg. Prior to change)
2	Smith, Bob	32	26	27	32	30	Oct-23	28.3
3								^Based on month of change (Oct. 23), should average Sep. 23, Aug 23 and Jul 23 ((26+27+32)/3=28.3)
Sheet1

Cell Formulas
Range	Formula
AA2	AA2	=AVERAGE(INDEX($U$1:$Y$2,2,MATCH($Z$2,$U$1:$Y$1,0)-1),INDEX($U$1:$Y$2,2,MATCH($Z$2,$U$1:$Y$1,0)-2),INDEX($U$1:$Y$2,2,MATCH($Z$2,$U$1:$Y$1,0)-3))

 

[Phuoc]

Or try this:

Book1
	T	U	V	W	X	Y	Z	AA
1	Name	Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Month of change	Baseline (3 month avg. Prior to change)
2	Smith, Bob	32	26	27	32	30	Oct-2023	28.3
3								^Based on month of change (Oct. 23), should average Sep. 23, Aug 23 and Jul 23 ((26+27+32)/3=28.3)
Sheet1

Cell Formulas
Range	Formula
AA2	AA2	=AVERAGEIFS(U2:Y2,$U$1:$Y$1,"<="&Z2,$U$1:$Y$1,">"&EDATE(Z2,-3))

 

[Fluff]

Another option

=AVERAGE(TAKE(FILTER(U2:Y2,$U$1:$Y$1<Z2),,-3))

 

[seasea123]

Thank you! How will these formula differ if there are multiple rows of data?

 

[Fluff]

What do you mean by multiple different rows?

 

[seasea123]

What do you mean by multiple different rows?

If there were a row 3, 4 and 5, etc. instead of just row 2.

 

[Fluff]

Should each row be averaged individually?

 

[seasea123]

Should each row be averaged individually?

Yes