work book on Excel 07

[Beth Gaines]

I want to average a group of percentages but want to exlude cells which have
the value 0 (some of the cells have 0 for the numerator and denominator).
What would the formula be? What I have so far is
=(F10+F13+F16+F19+F22+F26+F30)/7
What I want to do is exclude F19 for example if its value is 0 rather than
0%. Any suggestions?
Thanks much.

 

[Peo Sjoblom]

How many cells are we talking about? And is there any system to it? Your
cells seem to be every third cell until after F22, after that here is one
cell gap extra. If indeed it is every third cell you can use


=AVERAGE(IF((MOD(F10:F30,3)=1)*(F10:F30>0),F10:F30))

entered with ctrl + shift & enter


If your cells are like what you posted, try


=SUM(F10,F13,F16,F19,F22,F26,F30)/SUMPRODUCT(COUNTIF(INDIRECT({"F10","F13","F16","F19","F22","F26","F30"}),">0"))




--


Regards,


Peo Sjoblom

 

[JBeaucaire]

Try this:

=SUM(F10,F13,F16,F19,F22,F26,F30) / COUNTA(F10,F13,F16,F19,F22,F26,F30)

 

[Beth Gaines]

It worked. Thank you very much.

Try this:

=SUM(F10,F13,F16,F19,F22,F26,F30) / COUNTA(F10,F13,F16,F19,F22,F26,F30)
--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.

 

[Peo Sjoblom]

That would not exclude zero from the average

--


Regards,


Peo Sjoblom