Average Excluding Values

[anthonyexcel]

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!

=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 {
padding: 4px;
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>

 

[shg]

=averageifs(a1:a6, a1:a6, "<>3", a1:a6, "<>4", a1:a6, "<>6")

 

[MarcelBeug]

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

Try the following array formula:

=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 ",")

 

[anthonyexcel]

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

 

[shg]

You're welcome.