Average Excluding Values

anthonyexcel

Active Member
If I have the table below and I wanted to exclude certain values like 3,4,6 in the average. I know that the formula below (array entered) excludes just 6, how do I get it to exclude 3,4,6? Thanks in advance!
Code:
``=AVERAGE(IF(1-(A1:A6=6),A1:A6))``
<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>

1
2
3
4
5
6

<tbody>
</tbody>

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
=averageifs(a1:a6, a1:a6, "<>3", a1:a6, "<>4", a1:a6, "<>6")

While I was posting the following, shg posted a better solution in the meantime.

Try the following array formula:

Code:
``=AVERAGE(IF(1-ISNUMBER(MATCH(A1:A6,{3;4;6},0)),A1:A6))``

Remark: I'm not 100% sure about the part {3;4;6}. Maybe should be {3,4,6}. (In Europe we are using ";" as delimiter instead of ",")

Last edited:
Beautiful, thank you both! I appreciate all of your help!

You're welcome.

Replies
0
Views
584
Replies
7
Views
617
Replies
1
Views
329
Replies
2
Views
845
Replies
0
Views
240

1,207,261
Messages
6,077,363
Members
446,280
Latest member
Danielosama

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.

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