Moving Average for previous 2 days, current day, and 2 days after

[johnsonlnl29]

Hi -
I'm stuck and hoping someone here can assist. I'm working in Excel 365 with a table. I'm trying to find the 5 day average for previous 2 days, current day, and 2 days after. Example: for December 5 I would like Dec 3, 4,5,6,7 data to calculate my 5 day average. In addition, for days that don't have the 2 days after yet I'd like it to be #N/A until the data is there. This is a table that is updated daily via Power Query from an ACCESS query. If it's easier to do in ACCESS I could do that too it's just right now I'm doing these types of calcs in the Excel table. I'd prefer non VBA if possible as I'm not real strong in that area of Excel. I appreciate any and all help!

 

[SanjayGMusafir]

Since you haven't shared any data, I can only tell your solution is AverageIFS nested in IFS function.

 

[kevin9999]

As @SanjayGMusafir pointed out, some data would have been useful, and I agree with him that an IF/Averageif combination might be the way to go. There's probably a more elegant solution than this but here's one suggestion anyway:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	7-Dec-23	8-Dec-23	9-Dec-23	10-Dec-23	11-Dec-23	12-Dec-23	13-Dec-23	14-Dec-23	Average
2	Values 1	1	2	3	4	5	6			4
3	Values 2	1	2	3	4	5				#N/A
Sheet1

Cell Formulas
Range	Formula
J2:J3	J2	=IF(OR(INDEX(B2:I2,MATCH(TODAY()+1,$B$1:$I$1,0))="",INDEX(B2:I2,MATCH(TODAY()+2,$B$1:$I$1,0))=""),"#N/A",AVERAGEIFS(B2:I2,$B$1:$I$1,">="&TODAY()-2,$B$1:$I$1,"<="&TODAY()+2))

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm working in Excel 365

Please add that to your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this the sort of thing you are after?

23 12 10.xlsm
	A	B	C
1	Date	Value	5 day moving average
2	01-Dec-23	70
3	02-Dec-23	23
4	03-Dec-23	8	45.6
5	04-Dec-23	77	40.4
6	05-Dec-23	50	39
7	06-Dec-23	44	55
8	07-Dec-23	16	43.4
9	08-Dec-23	88	38.4
10	09-Dec-23	19	#N/A
11	10-Dec-23	25	#N/A
12	11-Dec-23		#N/A
13	12-Dec-23		#N/A
Moving average

Cell Formulas
Range	Formula
C2:C13	C2	=IF(COUNT(A$2:A2)>2,IF(B4<>"",AVERAGE(INDEX(B:B,SEQUENCE(5,,ROW()-2))),NA()),"")

 

[johnsonlnl29]

Hello -
Yes! @Peter_SSs you nailed it!
I made note next time to include a data set as example.
I updated my profile as noted.
Thanks again everyone ~
Angie

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

.. and thanks for updating your profile.