Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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)

share|improve this question

migrated from Sep 11 '12 at 12:10

This question came from our site for professional and enthusiast programmers.

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
up vote 5 down vote accepted

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.

share|improve this answer
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


share|improve this answer

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.

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .