# Averageifs statement only averaging last 5 %'s

#### MIRPAX

##### New Member
I am trying to find a way to average the last 5 entries based on the below criteria:
Column A - Name
Column B - Function
Column C - Quality %

So, I want to averageifs(C:C,A:A,"bob",B:B,"testing"), but I only want to consider the last five entries. I also want to exclude blanks.

I have tried something like this, but it is not working:
=AVERAGEIFS(INDEX(C:C,MATCH(9.99999999999999E+307,C:C)-5):INDEX(C:C,MATCH(9.99999999999999E+307,C:C)),A:A,"BOB",B:B,"Testing")
I get a value error.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Define Lrow in Name Manager as referring to:

=MATCH(9.99999999999999E+307,Sheet1!\$C:\$C)

Define name as referring to:

=Sheet1!\$A\$2:INDEX(Sheet1!\$A:\$A,Lrow)

Define function as referring to:

=Sheet1!\$B\$2:INDEX(Sheet1!\$B:\$B,Lrow)

Define quality as referring to:

=Sheet1!\$C\$2:INDEX(Sheet1!\$C:\$C,Lrow)

Adjust the sheet name to suit in the above definitions.

For the conditional average...

Control+shift+enter, not just enter:

=AVERAGE(IF(ROW(quality)>=LARGE(IF(name="bob",IF(function="testing",IF(ISNUMBER(1/quality),ROW(quality)))),MIN(5,SUM(IF(name="bob",IF(ISNUMBER(1/quality),1))))),IF(name="bob",IF(function="testing",IF(ISNUMBER(1/quality),quality)))))

Replies
3
Views
1K
Replies
3
Views
153
Replies
3
Views
740
Replies
16
Views
551
Replies
3
Views
537

1,196,480
Messages
6,015,449
Members
441,895
Latest member
Zululander

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back