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]

That's because the values in row 1 are still text & not dates.
Try

Fluff.xlsm
	T	U	V	W	X	Y	Z	AA
1	Name	01/07/2023	01/08/2023	09/01/2023	10/01/2023	11/01/2023	Month of Change	Baseline (3 Month Avg. Prior to Change)
2	Smith, Bob	32	26	27	32	30	Oct-23	29.8
3	Evans, Janet	30	23	32	22	40	Nov-23	31.3
Sheet6

Cell Formulas
Range	Formula
AA2:AA3	AA2	=AVERAGE(TAKE(FILTER(U2:Y2,DATEVALUE($U$1:$Y$1)<Z2),,-3))

I think this is closer but still having trouble converting the dates. They are showing as a date, not text but they are not showing up the same way yours are.

Mr Excel.xlsx
	T	U	V	W	X	Y	Z	AA
1	Name	7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023	Month of Change
2	Smith, Bob	32	26	27	32	30	10/1/2023	30
3	Evans, Janet	30	23	32	22	40	11/1/2023	31
Sheet1

Cell Formulas
Range	Formula
AA2:AA3	AA2	=AVERAGE(TAKE(FILTER(U2:Y2,DATEVALUE($U$1:$Y$1)<Z2),,-3))

 

[Fluff]

You have now got text in col Z rather than dates.

 

[seasea123]

I think this is closer but still having trouble converting the dates. They are showing as a date, not text but they are not showing up the same way yours are.

Mr Excel.xlsx
	T	U	V	W	X	Y	Z	AA
1	Name	7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023	Month of Change
2	Smith, Bob	32	26	27	32	30	10/1/2023	30
3	Evans, Janet	30	23	32	22	40	11/1/2023	31
Sheet1

Cell Formulas
Range	Formula
AA2:AA3	AA2	=AVERAGE(TAKE(FILTER(U2:Y2,DATEVALUE($U$1:$Y$1)<Z2),,-3))

Thank you! This worked.

 

[Fluff]

You're welcome & thanks for the feedback.