Conditional look up multiple criteria then average

[carlidee]

Hi

I plan on having a master tab that I will continue to add data to over time. I'm looking to create a summary tab that show the most recent 3 week's average per employee. Please see example data below. I provided four dates below, but would only want the average to be the three most recent weeks.

Thank you!

EXAMPLE OF MASTER DATA SOURCE TAB

Name	Count	Date
Dave	6	1/1/2021
Dave	11	2/1/2021
Dave	17	3/1/2021
Dave	20	4/1/2021
Tim	4	1/1/2021
Tim	23	2/1/2021
Tim	10	3/1/2021
Tim	12	4/1/2021
Kate	24	1/1/2021
Kate	12	2/1/2021
Kate	10	3/1/2021
Kate	6	4/1/2021

EXAMPLE OF DESIRED OUTPUT ON ANOTHER TAB:

Name	Most recent 3 weeks average
Dave	16​
Tim	15​
Kate	13​

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
1	Name	Count	Date		Name	Most recent 3 weeks average
2	Dave	6	5/31/2021		Dave	11.33333
3	Dave	11	5/24/2021		Tim	12.33333
4	Dave	17	5/17/2021		Kate	15.33333
5	Dave	20	5/10/2021
6	Tim	4	5/31/2021
7	Tim	23	5/24/2021
8	Tim	10	5/17/2021
9	Tim	12	5/10/2021
10	Kate	24	5/31/2021
11	Kate	12	5/24/2021
12	Kate	10	5/17/2021
13	Kate	6	5/10/2021
14
Sheet13

Cell Formulas
Range	Formula
F2:F4	F2	=AVERAGEIFS(B:B,A:A,E2,C:C,">="&TODAY()-21)

This just works on 3 weeks back from today. If you want to consider that a week starts on a Monday or some such thing, let me know.

 

[carlidee]

Try:

Book1
	A	B	C	D	E	F
1	Name	Count	Date		Name	Most recent 3 weeks average
2	Dave	6	5/31/2021		Dave	11.33333
3	Dave	11	5/24/2021		Tim	12.33333
4	Dave	17	5/17/2021		Kate	15.33333
5	Dave	20	5/10/2021
6	Tim	4	5/31/2021
7	Tim	23	5/24/2021
8	Tim	10	5/17/2021
9	Tim	12	5/10/2021
10	Kate	24	5/31/2021
11	Kate	12	5/24/2021
12	Kate	10	5/17/2021
13	Kate	6	5/10/2021
14
Sheet13

Cell Formulas
Range	Formula
F2:F4	F2	=AVERAGEIFS(B:B,A:A,E2,C:C,">="&TODAY()-21)

This just works on 3 weeks back from today. If you want to consider that a week starts on a Monday or some such thing, let me know.

This should do the trick - thank you!!

 

[Eric W]

Happy to help! Thanks for the feedback. ?