WUGNET, the Windows User Group Network
Your Complete Resource Center for "The Best" in Shareware, Computing Tips and Support, Windows Industry News... and much more!
Home Forums Shareware Windows Tips Hot Offers FREE Newsletters Arcade Contact Us About Partners
Search WUGNET: RSS Feeds RSS Feeds Advertise with WUGNET    |    Shareware eBooks
HomeHome FAQFAQ      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

work book on Excel 07

  
   Home -> Office -> New Users RSS
Next:  Style Type Char Char Car Car Char  
Author Message
Beth Gaines

External


Since: Nov 06, 2009
Posts: 1



(Msg. 1) Posted: Fri Nov 06, 2009 2:37 pm
Post subject: work book on Excel 07
Archived from groups: microsoft>public>excel>newusers (more info?)
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.
Back to top
Login to vote
Peo Sjoblom

External


Since: May 20, 2009
Posts: 16



(Msg. 2) Posted: Fri Nov 06, 2009 2:59 pm
Post subject: Re: work book on Excel 07 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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


"Beth Gaines" <Beth Gaines.TakeThisOut@discussions.microsoft.com> wrote in message
news:5E213AA8-1FD2-4ACB-8FF1-69617D2AC7EC@microsoft.com...
>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.
Back to top
Login to vote
JBeaucaire

External


Since: Oct 28, 2009
Posts: 3



(Msg. 3) Posted: Fri Nov 06, 2009 11:29 pm
Post subject: RE: work book on Excel 07 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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.


"Beth Gaines" wrote:

> 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.
Back to top
Login to vote
Beth Gaines

External


Since: Nov 09, 2009
Posts: 1



(Msg. 4) Posted: Mon Nov 09, 2009 8:34 am
Post subject: RE: work book on Excel 07 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
It worked. Thank you very much.

"JBeaucaire" wrote:

> 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.
>
>
> "Beth Gaines" wrote:
>
> > 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.
Back to top
Login to vote
Peo Sjoblom

External


Since: May 20, 2009
Posts: 16



(Msg. 5) Posted: Mon Nov 09, 2009 10:23 am
Post subject: Re: work book on Excel 07 [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
That would not exclude zero from the average

--


Regards,


Peo Sjoblom


"JBeaucaire" <jbeau.TakeThisOut@madrocketscientist.com.NOSPAM> wrote in message
news:DD6BC629-CB5F-47A5-998C-068BD1E1F5D2@microsoft.com...
> 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.
>
>
> "Beth Gaines" wrote:
>
>> 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.
Back to top
Login to vote
Display posts from previous:   
       Home -> Office -> New Users All times are: Eastern Time (US & Canada) (change)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Categories:
 Windows XP
 Windows Vista
 Windows Other
  Office
 Office Other
 Security
  • Home |
  • Shareware |
  • Windows Tips |
  • Hot Offers |
  • FREE Newsletters |
  • Arcade |
  • Forums |
  • eBooks |
  • About WUGNET |
  • Partners |
  • Contact

  • WUGNET Privacy Policy |
  • Link to WUGNET |
  • IT Support