Offset and averageif question.

[pjmoorhouse71]

Good afternoon,

Hope someone can assist - I have the following formula which works perfectly to average the last 5 results of data in column D. Column D is a productivity rate and just a regular number

=AVERAGE(OFFSET(Stats!D3,COUNTA(Stats!D3:D100)-5,0,5.1))

However, column C has one of 4 entries - let's say either a,b,c or d (the name of a machine) - I want to adapt the above forumla so that it calculates the average of the last 5 numbers in column D based on what is column C. So I can see the average of the last 5 entries in column D where column C has "a" in it or "b" etc.

Hope this is clear and someone can help.

Thanks.

 

[AndrewHoffenberg]

Can you add an additional column in E? If so I would arrange the data like this:

	C	D	E	F	G	H
3	A	100	5
4	C	2	5
5	A	5	4
6	C	3	4
7	A	6	3		Machine Name =	A
8	A	10	2		Average =	5.6
9	A	2	1
10	A	5	0
11	C	1	0

<tbody>
</tbody>

E3	=COUNTIF(C:C,$H$7)-COUNTIF($C$3:C3,$H$7)
H7	User input for the name of the machine
H8	=AVERAGEIFS(D3:D11,C3:C11,H7,E3:E11,"<"&5)

<tbody>
</tbody>

Hope this helps!
-Andrew

 

[Eric W]

Welcome to the board.

Another possibility:

=AVERAGE(IF((C3:C100="a")*(ROW(C3:C100)>=LARGE(IF(C3:C100="a",ROW(C3:C100)),5)),D3:D100))

confirmed with Control+Shift+Enter.