Average of values in Excel from different cells excluding zero

Question

I have a set of values that I want to find the average for. The values are in different cells, not near each other. The value of zero should not be considered.

• E.g.1 in cell A1 I have 5, in cell C4 I have 7, in cell Z2 I have 0.
• E.g.2 in cell A1 I have 5, in cell C4 I have 7, in cell Z2 I have 8.

---

• In E.g. 1 the average should be (5+7)/2
• In E.g. 2 the average should be (5+7+8)/3

I would like to achieve this using Excel functions with no hard coding of any values. I am aware that if the values were next to each-other, e.g. in cells A1,A2,A3 I would do =AVERAGEIF(A1:A3,">0")

Answer 1

I'm going to assume that the condition 'not zero' can be hardcoded. Also that the cell locations can be, since they're disjoint.

You can implement your own:

=SUM(A1, C4, Z2)/SUM(A1>0, C4>0, Z2>0)

It's a little cumbersome, but Excel doesn't appear to let you use the *IF functions on disjoint ranges. You could always use a hidden cell range, but it sounds like you don't want to do that.

Answer 2

Overkill for 3 cells but this approach can be extended to use discontinuous ranges, if required, rather than individual cells

=SUM(E1,C4,Z2)/INDEX(FREQUENCY((E1,C4,Z2),0),2)

Answer 3

This works for your example:

=AVERAGEIF(INDIRECT({"A1","C4","Z2"}),">0")