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)

  • Values in different columns, or same column? – Prasanna Sep 11 '12 at 6:47
  • see image and edit – php-b-grader Sep 11 '12 at 6:56
  • For future reference this question is better asked at Super User - it is not programming. – brettdj Sep 11 '12 at 7:04
  • what version of excel? – SeanC Sep 11 '12 at 14:25

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.

  • This is going to be difficult for the highlighted cells, as you would need a criteria where several cities are checked to decide if they should be considered in the range – SeanC Sep 11 '12 at 15:44
  • I like this for the Tier 1 & Tier 2 but it still leaves out the CBD suburbs. I might have to do CBD differently – php-b-grader Sep 11 '12 at 22:46
  • I have just changed the heading to CBD and use an alternate method of calculating the cell values hence making this solution work perfectly. – php-b-grader Sep 11 '12 at 22:55
  • If CBD is all those that aren't Tier 1 or Tier 2 you could try this version with a "wildcard" leaving headers unaltered =AVERAGEIFS(A3:Z3,A$2:Z$2,"<>Tier*",A3:Z3,">0") – barry houdini Sep 11 '12 at 23:00

In any version., you can use this type of formula to AVERAGE non-contiguous cells without zeroes (assumes positive values only)


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



This looks horrible, and also requires Excel 2007 or greater:


but it will average the selected cells (no criteria needed for SUM as adding zero or not doesn't alter the total.

  • I tried this but was getting errors so I gave up. I think it is the only solution that will work . – php-b-grader Sep 11 '12 at 22:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.