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

An undocumented feature is that you can join disjoint ranges with a colon.

AVERAGEIF(A1:A1:A5:A5:A7:A7, ">0")

In other words, you can string together ranges of the form start:end:start2:end2, by using colons. If using single cells, you have to use that cell as both start and end, as shown

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

Answer 4

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.