'Average of values in Excel from different cells excluding zero
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
A1I have 5, in cellC4I have 7, in cellZ2I have 0. - E.g.2 in cell
A1I have 5, in cellC4I have 7, in cellZ2I 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")
Solution 1:[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.
Solution 2:[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)
Solution 3:[3]
This works for your example:
=AVERAGEIF(INDIRECT({"A1","C4","Z2"}),">0")
Solution 4:[4]
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | James K |
| Solution 2 | |
| Solution 3 | Carol |
| Solution 4 | JonB |