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!

 

[alansidman]

cannot manipulate data in a picture. please reload data using xl2bb. See my signature for instructions.

 

[RasGhul]

Hi Mathew,

Maybe this setup, then your Workdays header row won't include weekends;

Book1
	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
6	19/11/2021			1-Nov-21	2-Nov-21	3-Nov-21	4-Nov-21	5-Nov-21	8-Nov-21	9-Nov-21	10-Nov-21	11-Nov-21	12-Nov-21	15-Nov-21	16-Nov-21	17-Nov-21	18-Nov-21	19-Nov-21	22-Nov-21	23-Nov-21	24-Nov-21	25-Nov-21	26-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()
G6:AA6	G6	=WORKDAY($F$6,COLUMNS($G$6:G6))
D11	D11	=TODAY()-1
D12	D12	=WORKDAY(D11,-4)
C14	C14	=AVERAGEIFS($F$9:$AA$9,$F$6:$AA$6,">="&$D$12,$F$6:$AA$6,"<="&$D$11,$F$9:$AA$9,"<>""")

 

[MatthewChung]

cannot manipulate data in a picture. please reload data using xl2bb. See my signature for instructions.

Hi alan

I have followed the instructions but cannot seem to get the Xl2bb tab to appear in my excel.

 

[MatthewChung]

Hi Mathew,

Maybe this setup, then your Workdays header row won't include weekends;

Book1
	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
6	19/11/2021			1-Nov-21	2-Nov-21	3-Nov-21	4-Nov-21	5-Nov-21	8-Nov-21	9-Nov-21	10-Nov-21	11-Nov-21	12-Nov-21	15-Nov-21	16-Nov-21	17-Nov-21	18-Nov-21	19-Nov-21	22-Nov-21	23-Nov-21	24-Nov-21	25-Nov-21	26-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()
G6:AA6	G6	=WORKDAY($F$6,COLUMNS($G$6:G6))
D11	D11	=TODAY()-1
D12	D12	=WORKDAY(D11,-4)
C14	C14	=AVERAGEIFS($F$9:$AA$9,$F$6:$AA$6,">="&$D$12,$F$6:$AA$6,"<="&$D$11,$F$9:$AA$9,"<>""")

Hi Ras

Thank you for the response. The screenshot I attached was a bad example sorry. Sometimes there will be productivity over the weekend and sometimes there won't. Is there a possible work around for this scenario?

 

[MatthewChung]

cannot manipulate data in a picture. please reload data using xl2bb. See my signature for instructions.

Hi alan

I managed to get it to work after watching the video.

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	18-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			61	91	61	3	13			17	16	43	43	20			56	56
10
11
12		last work week
13		22.14286
Sheet1

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

 

[alansidman]

Can you explain how you come up with 60. When I average the last 5 working days I get 43.6. What am I missing in understanding your needs. Your layout is not conducive to analysis. If you unpivot your data so that it is columnar (normalized) then you can do averages and/or pivot tables which will allow you more flexibility.

 

[MatthewChung]

Can you explain how you come up with 60. When I average the last 5 working days I get 43.6. What am I missing in understanding your needs. Your layout is not conducive to analysis. If you unpivot your data so that it is columnar (normalized) then you can do averages and/or pivot tables which will allow you more flexibility.

Hi alan

For this scenario, I would like to determine the last five cells with values in them starting from 18 November 21 so it would be something like the below / attached image.

18 Nov 21 = 3
17 Nov 21 = 61
16 Nov 21 = 91
15 Nov 21 = 61
12 Nov 21 = 84

 

[RasGhul]

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

 

[MatthewChung]

Hi RasGhul

That ALMOST worked! I put some values in the Saturday and Sunday column and your formula skips these days.

Book1.xlsx
	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	AJ
1
2		18-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	57
16
17
Sheet1

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