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:
[TABLE="class: grid, width: 596, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: right"]Machine Name =[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: right"]Average =[/TD]
[TD="align: center"]5.6[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]E3[/TD]
[TD]=COUNTIF(C:C,$H$7)-COUNTIF($C$3:C3,$H$7)[/TD]
[/TR]
[TR]
[TD]H7[/TD]
[TD]User input for the name of the machine[/TD]
[/TR]
[TR]
[TD]H8[/TD]
[TD]=AVERAGEIFS(D3:D11,C3:C11,H7,E3:E11,"<"&5)[/TD]
[/TR]
</tbody>[/TABLE]
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.