work book on Excel 07

  • Thread starter Thread starter Beth Gaines
  • Start date Start date
B

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.
 
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
 
Try this:

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