SbElectric   1600 pts.  |   May 2 2009  2:49AM GMT

If you want to find the average for all 4 cells (and excluding if sum is 0) then the suggestion by Carlsdt should do the trick. But if you want to exclude if any of the cells contain “0” then you need to do some more coding. Easiest may be to count the number of cells containing zero & then dividing by appropriate number.

Let us know your actual specifications/requirements.

Kccrosser   1850 pts.  |   May 4 2009  8:40PM GMT

Assuming you can define an Excel range containing the cells, such as “myRange”, then:

sum(myRange)/countif(myRange, “>0″)

will yield the average, ignoring zeros.

However, your formula (sum(range) / average(range)) is mathematically equal to the count of the cells, and (sum(range) / (sum(range) / average_excluding_zeros(range))) is just the count of non-zero cells.

Is this what you are trying to compute?

SbElectric   1600 pts.  |   May 6 2009  3:07AM GMT

Yes. Carlosdl, kccrosser, and myself are thinking along the same line. In my last post, I enquired for more specification – have not seen any.

Assume data are in cells A6 thru A11. Then the following code (say in Cell A13) will provide the needed answer.
=IF (SUM(A6:A11)=0,”N/A”,SUM(A6:A11)/(COUNT(A6:A11)-COUNTIF(A6:A11,0)))

Findsarfaraz   1395 pts.  |   May 14 2009  10:22AM GMT

Just to add to this..
There is Averageif() Function which is similar to countif which will useful in such scenarion.

Sarfaraz Ahmed
MS Excel help