Excel 2007 – how to add a average formula to that cell/s that ignoring 0%’s
Ok 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))
Thx
Jared
Software/Hardware used:
Software/Hardware used:
ASKED:
April 30, 2009 5:19 PM
UPDATED:
May 14, 2009 10:22 AM
Answer Wiki:
How about this ?
<pre>=IF(SUM(U171,U122,U74,U27)=0,#N/A,AVERAGE(U171,U122,U74,U27))</pre>
Is that what you mean ?
(This question is related to <a href="http://itknowledgeexchange.techtarget.com/itanswers/excel-2007-how-do-i-ignore-zero-s-for-a-graph/">this one</a>).
To see all answers submitted to the Answer Wiki: View Answer History.
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.
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?
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)))
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