Average of last 5 nonblank values in row with matching date

[MatthewChung]

Hi Everyone!

After spending days and days trying to figure out this problem without any success, I decided to seek further assistance by posting it here. I understand there are some very similar posts which I have been through but it got out of hand very quickly...

I am working in a table with daily productivity rates and would like to determine the average of the last 5 working days (excluding blanks) based on a moving date. The formula below was what I started with but it included blanks as well which returned an average of 54 when I wanted it to return an average of 60.

=AVERAGE(OFFSET(E9,0,MATCH(B2,F6:AI6,0),1,-5))

As soon as I added additional criteria in the formula below, it returned strange values and became way to difficult for me to understand.

=AVERAGE(OFFSET(E9,0,MATCH(B2,F6:AI6,0),1,SMALL(IF(ISNUMBER(F9:AI9),ROW(F9:AI9)),MIN(5,COUNT(F9:AI9)))))

Any help would be very much appreciated. Thank you!

 

[MatthewChung]

This works with weekends included

Average of last 5 nonblank values in row with matching date_Matthew Chung.xlsx
	C	D	E	F	G	H	I	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
6	19/11/2021			1-Nov-21	2-Nov-21	3-Nov-21	4-Nov-21	5-Nov-21	6-Nov-21	7-Nov-21	8-Nov-21	9-Nov-21	10-Nov-21	11-Nov-21	12-Nov-21	13-Nov-21	14-Nov-21	15-Nov-21	16-Nov-21	17-Nov-21	18-Nov-21	19-Nov-21	20-Nov-21	21-Nov-21	22-Nov-21	23-Nov-21	24-Nov-21	25-Nov-21	26-Nov-21	27-Nov-21	28-Nov-21	29-Nov-21	30-Nov-21
7
8
9			productivity	71	1	48	65			31	43	85	21	93	84			61	91	61	3	13			17	16	43	43	20		56	56
10
11	Yesterday	18/11/2021
12	5 days Prior	12/11/2021
13	Last Week
14	60
Sheet1

Cell Formulas
Range	Formula
C6	C6	=TODAY()
D11	D11	=TODAY()-1
D12	D12	=WORKDAY(D11,-4)
C14	C14	=AVERAGEIFS($F$9:$AI$9,$F$6:$AI$6,">="&$D$12,$F$6:$AI$6,"<="&$D$11,$F$9:$AI$9,"<>""")

Correct version here sorry! With the WORKDAY function you put in, the formula averages the workdays including the Saturday productivity which is across 6 days and not 5.

 

[RasGhul]

if we force the previous 5th day 13 - 18th the average for positive cells is 54?

We only returned 60 when we included the 12th - across 6 days - is this ok?

 

[bebo021999]

Back to 7 days, 5-working days are included, no more, no less.

Book1
	A	B	C	D	E	F	G	H	I	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
1
2
3
4
5						Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
6						01/11/2021	02/11/2021	03/11/2021	04/11/2021	05/11/2021	06/11/2021	07/11/2021	08/11/2021	09/11/2021	10/11/2021	11/11/2021	12/11/2021	13/11/2021	14/11/2021	15/11/2021	16/11/2021	17/11/2021	18/11/2021	19/11/2021	20/11/2021	21/11/2021	22/11/2021	23/11/2021	24/11/2021	25/11/2021	26/11/2021	27/11/2021	28/11/2021	29/11/2021	30/11/2021
7
8
9					productivity	71	1	48	65	31			43	85	21	93	84	1		61	91	61	3	13		9	17	16	43	43	20			56	56
10
11
12
13
14
15			last work week	60
Sheet1

Cell Formulas
Range	Formula
F5:AI5	F5	=TEXT(F6,"ddd")
D15	D15	=AVERAGEIFS($F9:$AI9,$F$6:$AI$6,"<"&TODAY(),$F$6:$AI$6,">="&TODAY()-7,$F$5:$AI$5,"<>s*")

Above ir not take holidays into account. Tell me for update solution if holidays are counted.

 

[RasGhul]

Argh my values were against the wrong dates

=AVERAGEIFS($F$9:$AI$9,$F$6:$AI$6,">="&$C$6-5,$F$6:$AI$6,"<="&$C$6,$F$9:$AI$9,"<>""") = 60

13th 84
15th 61
16th 91
17th 61
18th 3

B2 -1 would've been the 17th.

 

[MatthewChung]

if we force the previous 5th day 13 - 18th the average for positive cells is 54?

We only returned 60 when we included the 12th - across 6 days - is this ok?

30-Nov-21
				mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue
				1-Nov-21	2-Nov-21	3-Nov-21	4-Nov-21	5-Nov-21	6-Nov-21	7-Nov-21	8-Nov-21	9-Nov-21	10-Nov-21	11-Nov-21	12-Nov-21	13-Nov-21	14-Nov-21	15-Nov-21	16-Nov-21	17-Nov-21	18-Nov-21	19-Nov-21	20-Nov-21	21-Nov-21	22-Nov-21	23-Nov-21	24-Nov-21	25-Nov-21	26-Nov-21	27-Nov-21	28-Nov-21	29-Nov-21	30-Nov-21
			productivity	71	1	48	65	31			43	85	21	93	84	15		61	91	61	3	13		9	17	16	43	43	20			56	56
	last work week	40.5

Hi RasGhul

Thank you for your response once again. I have entered some data in Saturdays, Sundays in separate weeks and left both of them some weeks completely blank. In this scenario the average of the previous 5 cells should return a value of 35.6 but the formula says 40.5.

 

[bebo021999]

@RasGhul, blank cell does not effect to average. I believe he does not want to do average for Sat/Sun if there was productivity in weekend.

 

[MatthewChung]

@RasGhul, blank cell does not effect to average. I believe he does not want to do average for Sat/Sun if there was productivity in weekend.

Hi bebo

The averages need to be taken if there is data in the previous 5 cells (weekends included if there is productivity).

 

[RasGhul]

Did you adjust the end date in the formula? or B2 should be TODAY()-1(18/11/21)

=AVERAGEIFS($F$9:$AI$9,$F$6:$AI$6,">="&$C$6-5,$F$6:$AI$6,"<="&today()-1,$F$9:$AI$9,"<>""")

 

[bebo021999]

Hi bebo

The averages need to be taken if there is data in the previous 5 cells (weekends included if there is productivity).

Including WK should be simpler:
=AVERAGEIFS($F9:$AI9,$F$6:$AI$6,"<"&B2,$F$6:$AI$6,">="&B2-7)

 

[MatthewChung]

Did you adjust the end date in the formula? or B2 should be TODAY()-1(18/11/21)

=AVERAGEIFS($F$9:$AI$9,$F$6:$AI$6,">="&$C$6-5,$F$6:$AI$6,"<="&today()-1,$F$9:$AI$9,"<>""")

Sorry, I did not copy the correct data.

Book1.xlsx
	B	C	D	E	F	G	H	I	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
2	30-Nov-21
3
4
5					mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue	wed	thu	fri	sat	sun	mon	tue
6					1-Nov-21	2-Nov-21	3-Nov-21	4-Nov-21	5-Nov-21	6-Nov-21	7-Nov-21	8-Nov-21	9-Nov-21	10-Nov-21	11-Nov-21	12-Nov-21	13-Nov-21	14-Nov-21	15-Nov-21	16-Nov-21	17-Nov-21	18-Nov-21	19-Nov-21	20-Nov-21	21-Nov-21	22-Nov-21	23-Nov-21	24-Nov-21	25-Nov-21	26-Nov-21	27-Nov-21	28-Nov-21	29-Nov-21	30-Nov-21
7
8
9				productivity	71	1	48	65	31			43	85	21	93	84	15		61	91	61	3	13		9	17	16	43	43	20			56	56
10
11
12
13
14
15		last work week	40.5
Sheet1

Cell Formulas
Range	Formula
D15	D15	=AVERAGEIFS($F$9:$AI$9,$F$6:$AI$6,">="&WORKDAY(B2,-4),$F$6:$AI$6,"<="&B2-1,$F$9:$AI$9,"<>""")

I substituted TODAY() with a cell reference so the date can be manually adjusted.