Average Last n Values with multiple conditions

[Jabe]

Hi all,

I'm trying to average the last 5 values in a column after a number of conditions are met. In the attached example, is there a formula that would find the average of the number of days John's last 2 designs took?

Person	Action	Days
Adam	Design	1
John	Design	7
Charlotte	Design	4
Adam	Build	30
Danny	Build	27
Paula	Design	3
John	Build	41
Danny	Design	5
Charlotte	Design	6
Adam	Build	32
Paula	Build	36
John	Build	33
Charlotte	Build	29
John	Design	2
Danny	Design	4
Adam	Build	19
Paula	Build	27
Adam	Design	5
Charlotte	Design	4
John	Design	1
Danny	Build	34

I'm thinking something with AVERAGEIFS and TAKE but I can't seem to get it to work myself!

 

[etaf]

how about
=AVERAGE(TAKE(FILTER(C2:C22,(A2:A22="john")*(B2:B22="design")),-2))
will be the last 2

Book2
	A	B	C	D	E	F	G	H
1	Person	Action	Days		average		List for Info ONLY
2	Adam	Design	1		1.5		2
3	John	Design	7				1
4	Charlotte	Design	4
5	Adam	Build	30
6	Danny	Build	27
7	Paula	Design	3
8	John	Build	41
9	Danny	Design	5
10	Charlotte	Design	6
11	Adam	Build	32
12	Paula	Build	36
13	John	Build	33
14	Charlotte	Build	29
15	John	Design	2
16	Danny	Design	4
17	Adam	Build	19
18	Paula	Build	27
19	Adam	Design	5
20	Charlotte	Design	4
21	John	Design	1
22	Danny	Build	34
23
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(TAKE(FILTER(C2:C22,(A2:A22="john")*(B2:B22="design")),-2))
G2:G3	G2	=(TAKE(FILTER(C2:C22,(A2:A22="john")*(B2:B22="design")),-2))
Dynamic array formulas.

 

[Fluff]

I'm trying to average the last 5 values in a column

Then why are you trying to

average of the number of days John's last 2 designs took

As you haven't given the criteria, maybe

=LET(f,TAKE(FILTER(A2:C100,B2:B100="design"),-5),AVERAGE(FILTER(f,TAKE(f,,1)="john")))

 

[Jabe]

Then why are you trying to

As you haven't given the criteria, maybe

=LET(f,TAKE(FILTER(A2:C100,B2:B100="design"),-5),AVERAGE(FILTER(f,TAKE(f,,1)="john")))

Apologies, my original post was confusing. I can't publish my actual data on here so had to make a representative example and making enough data to match my actual problem was too time consuming!

 

[Jabe]

how about
=AVERAGE(TAKE(FILTER(C2:C22,(A2:A22="john")*(B2:B22="design")),-2))
will be the last 2

Book2
	A	B	C	D	E	F	G	H
1	Person	Action	Days		average		List for Info ONLY
2	Adam	Design	1		1.5		2
3	John	Design	7				1
4	Charlotte	Design	4
5	Adam	Build	30
6	Danny	Build	27
7	Paula	Design	3
8	John	Build	41
9	Danny	Design	5
10	Charlotte	Design	6
11	Adam	Build	32
12	Paula	Build	36
13	John	Build	33
14	Charlotte	Build	29
15	John	Design	2
16	Danny	Design	4
17	Adam	Build	19
18	Paula	Build	27
19	Adam	Design	5
20	Charlotte	Design	4
21	John	Design	1
22	Danny	Build	34
23
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(TAKE(FILTER(C2:C22,(A2:A22="john")*(B2:B22="design")),-2))
G2:G3	G2	=(TAKE(FILTER(C2:C22,(A2:A22="john")*(B2:B22="design")),-2))
Dynamic array formulas.

This seems to work, thanks! I have some values (in column C in the example) which are "NA" is there a way to skip them when doing the TAKE? So it would only TAKE the last 5 numeric values?

 

[Jabe]

This seems to work, thanks! I have some values (in column C in the example) which are "NA" is there a way to skip them when doing the TAKE? So it would only TAKE the last 5 numeric values?

I think I found a solution for anyone that's interested:
=AVERAGE(TAKE(FILTER(C2:C22,(A2:A22="john")*(B2:B22="design")*NOT(C2:C22="NA")),-2))

 

[etaf]

glad you solved