LinkBack URL
About LinkBacks
Hi
In the attached sample spreadsheet, how do I get the average for March-11 to July-11 for all Males who have "New or Existing" status of "E". I want to disregard zeros or blank cells.
Thanks.
ltsolis
Hi
In the attached sample spreadsheet, how do I get the average for March-11 to July-11 for all Males who have "New or Existing" status of "E". I want to disregard zeros or blank cells.
Thanks.
ltsolis
Probably a better way to do it with AVERAGEIFS or SUMIFS but using SUMPRODUCT
=SUMPRODUCT(--(SampleData[New or Existing
(N/E)]="E")*SampleData[[March-11]:[Jul-11]])/SUMPRODUCT(--(SampleData[New or Existing
(N/E)]="E")*(SampleData[[March-11]:[Jul-11]]>0))
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Thanks ChemistB
How do I fit in the other criteria of "S**=M"?
ltsolis
Oops, missed that part
=SUMPRODUCT(--(SampleData[New or Existing
(N/E)]="E")*(SampleData[***]="M")*SampleData[[March-11]:[Jul-11]])/SUMPRODUCT(--(SampleData[New or Existing
(N/E)]="E")* (SampleData[***]="M")*(SampleData[[March-11]:[Jul-11]]>0))
AVERAGEIFS(SampleData[Total],SampleData[New or Existing
(N/E)],"E",SampleData[March-11],"<>"&"",SampleData[March-11],"<>"&0,SampleData[April-11],"<>"&"",SampleData[April-11],"<>"&0)
this would only cover march and april, but you can add on may junes etc into the formula
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks