I need to calculate the average of multiple cells and I want to exclude values = 0.
The cells are not sequential so I cannot use AVERAGEIF(A1:A10, ">0")
I need to individually select the cells however, I want to copy the formula across multiple columns so do not want exclude any (manually)
How do I do this?
An example of the data is seen here:
I want to calculate an average of all rows for Tier 1, Tier 2 and CBD (excluding 0)
3 Answers
Use AVERAGEIFS, which lets you select criteria for averaging:
=AVERAGEIFS(A3:Z3,A$2:Z$2,"=Tier 1",A3:Z3,">0")
Then, when specifying the range for the criteria, be sure you lock the row number. (A$2:Z$2, rather than simply specifying the range as A2:Z2.) Otherwise, when you propogate down for subsequent row averages, Excel will (correctly) attempt to populate it as A3:Z3 and throw out a DIV/0 error.
In any version., you can use this type of formula to AVERAGE non-contiguous cells without zeroes (assumes positive values only)
=SUM(A2,F3,Z5)/MAX(1,INDEX(FREQUENCY((A2,F3,Z5),0),2))
where A2, F3 and Z5 are the cells to average
Formula returns zero if there are no non-zero values
You can name your range of non-contiguous cells, if you wish, and then use
=SUM(Range1)/MAX(1,INDEX(FREQUENCY(Range1,0),2))
This looks horrible, and also requires Excel 2007 or greater:
=SUM(A2,D2,G2,J2,M2,P2,S2,V2)/COUNTIFS(A2,"<>0",D2,"<>0",G2,"<>0",J2,"<>0",M2,"<>0",P2,"<>0",S2,"<>0",V2,"<>0")
but it will average the selected cells (no criteria needed for SUM as adding zero or not doesn't alter the total.
