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.

 

[Fluff]

Then just drag the formula I suggested down.

 

[seasea123]

It does not look like these formulas work unfortunately. Can you attach the excel file with the formula instead of an image?

 

[Fluff]

It would be better if you posted some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[seasea123]

It would be better if you posted some sample data, rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you! I am trying to download it, but can't becuase it says it cannot open in a protected view.

 

[seasea123]

Does this work?

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	Baseline (3 Month Avg. Prior to Change)
2	Smith, Bob	32	26	27	32	30	Oct-23
3	Evans, Janet	30	23	32	22	40	Nov-23
4
Sheet 1

 

[Fluff]

Thanks for that.
It appears that the "dates" in U1:Y1 are actually text rather than real dates. The simplest option is to change them to proper dates.

 

[seasea123]

Thanks for that.
It appears that the "dates" in U1:Y1 are actually text rather than real dates. The simplest option is to change them to proper dates.

Thank you! I tried that and am receiving a "CALC" error. Please see the table below.

Mr Excel.xlsx
	T	U	V	W	X	Y	Z	AA
7	Name	7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023	Month of Change	Baseline (3 Month Avg. Prior to Change)
8	Smith, Bob	32	26	27	32	30	10/1/2023	#CALC!
9	Evans, Janet	30	23	32	22	40	11/1/2023	#CALC!
10								#CALC!
Sheet 1

Cell Formulas
Range	Formula
AA8:AA10	AA8	=AVERAGE(TAKE(FILTER(U8:Y8,$U$1:$Y$1<Z8),,-3))

 

[Fluff]

Those still appear to be text & not dates.
Is that a normal range or a table?

 

[seasea123]

Those still appear to be text & not dates.
Is that a normal range or a table?

I converted to a range instead of a table but the formula is not calculating correctly still. Can you please assist?

Mr Excel.xlsx
	A	B	C	D	E	F	G	H
2	Name	7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023	Month of Change
3	Smith, Bob	32	26	27	32	30	10/1/2023	32
4	Evans, Janet	30	23	32	22	40	11/1/2023	31
Sheet1

Cell Formulas
Range	Formula
H3:H4	H3	=AVERAGE(TAKE(FILTER(B3:F3,$B$2:$F$2<=G3),,-3))

 

[Fluff]

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))