Excel AVERAGEIF value > 0

microsoft excelmicrosoft-excel-2010

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:
enter image description here

I want to calculate an average of all rows for Tier 1, Tier 2 and CBD (excluding 0)

Best Answer

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.

Related Question