## Excel 2007 – how to add a average formula to that cell/s that ignoring 0%’s

185 pts.
Tags:
Excel 2007
Microsoft Excel
Microsoft Excel formulas
Okay I figured out how to ignore zero %'s for a graph. Now what I'm stuck on is how to add a average formula to that cell/s This is what I had:
`=IF(SUM(U171,U122,U74,U27)=0,#N/A,(SUM(U171,U122,U74,U27),(AVERAGE(U171,U122,U74,U27)) `
Thanks,
Jared

Thanks. We'll let you know when a new response is added.

`=IF(SUM(U171,U122,U74,U27)=0,#N/A,AVERAGE(U171,U122,U74,U27))`

Is that what you mean?

(This question is related to this one: Excel 2007 – How do I ignore zero %’s for a graph?)

## Discuss This Question: 4 Replies

Thanks. We'll let you know when a new response is added.
• 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.
report
• 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?
report
• 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)))
report
• 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