Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com 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? –  Sean Cheshire Sep 11 '12 at 14:25

3 Answers 3

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
    
(i) ‘AVERAGEIFS` is not available in early versions of Excel and (ii) what about CBD (I think stands for Central Business District)? But +1 as is a good answer, in my view, for most such purposes. –  pnuts Sep 11 '12 at 15:04
    
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 –  Sean Cheshire 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)

=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))

share|improve this answer

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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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