Embeded Formula

[dharmalife87]

This will take a mixture of at least HLOOKUP and Average

I have a table of 2 columns; 1 column is dates from Jan. 1st to Dec 31st; 2nd column is inches of rain on each day. Several are 0 inches but I am needing to take the the average of each month of only the days that have greater than 0 inches of rain.

Getting the average of each month is easy but canceling out the 0 inch days is throwing me off right now.

 

[JoeMo]

If your dates are in M1:M365, and rainfall in N1:N365 then average for January of only the days that had rain is:

=SUMPRODUCT((MONTH($M$1:$M$365)=1)*($N$1:$N$365>0),$N$1:$N$365)/SUMPRODUCT((MONTH($M$1:$M$365)=1)*($N$1:$N$365>0))

Change the 1 to 2 for February and so on ....
If there is no rainfall in the specified month, this will return #DIV/0!

 

[dharmalife87]

Thank You JoeMo;
This is the formula I ended up with
=AVERAGE(SUMPRODUCT((MONTH('2003'!$A$3:$A$367)=8)*('2003'!$B$3:$B$367>0),'2003'!$B$3:$B$367)/SUMPRODUCT((MONTH('2003'!$A$3:$A$367)=8)*('2003'!$B$3:$B$367>0)),SUMPRODUCT((MONTH('2004'!$A$3:$A$368)=8)*('2004'!$B$3:$B$368>0),'2004'!$B$3:$B$368)/SUMPRODUCT((MONTH('2004'!$A$3:$A$368)=8)*('2004'!$B$3:$B$368>0)),SUMPRODUCT((MONTH('2005'!$A$3:$A$367)=8)*('2005'!$B$3:$B$367>0),'2005'!$B$3:$B$367)/SUMPRODUCT((MONTH('2005'!$A$3:$A$367)=8)*('2005'!$B$3:$B$367>0)),SUMPRODUCT((MONTH('2006'!$A$3:$A$367)=8)*('2006'!$B$3:$B$367>0),'2006'!$B$3:$B$367)/SUMPRODUCT((MONTH('2006'!$A$3:$A$367)=8)*('2006'!$B$3:$B$367>0)),SUMPRODUCT((MONTH('2007'!$A$3:$A$367)=8)*('2007'!$B$3:$B$367>0),'2007'!$B$3:$B$367)/SUMPRODUCT((MONTH('2007'!$A$3:$A$367)=8)*('2007'!$B$3:$B$367>0)),SUMPRODUCT((MONTH('2008'!$A$3:$A$368)=8)*('2008'!$B$3:$B$368>0),'2008'!$B$3:$B$368)/SUMPRODUCT((MONTH('2008'!$A$3:$A$368)=8)*('2008'!$B$3:$B$368>0)),SUMPRODUCT((MONTH('2009'!$A$3:$A$367)=8)*('2009'!$B$3:$B$367>0),'2009'!$B$3:$B$367)/SUMPRODUCT((MONTH('2009'!$A$3:$A$367)=8)*('2009'!$B$3:$B$367>0)),SUMPRODUCT((MONTH('2010'!$A$3:$A$367)=8)*('2010'!$B$3:$B$367>0),'2010'!$B$3:$B$367)/SUMPRODUCT((MONTH('2010'!$A$3:$A$367)=8)*('2010'!$B$3:$B$367>0)),SUMPRODUCT((MONTH('2011'!$A$3:$A$367)=8)*('2011'!$B$3:$B$367>0),'2011'!$B$3:$B$367)/SUMPRODUCT((MONTH('2011'!$A$3:$A$367)=8)*('2011'!$B$3:$B$367>0)),SUMPRODUCT((MONTH('2012'!$A$3:$A$368)=8)*('2012'!$B$3:$B$368>0),'2012'!$B$3:$B$368)/SUMPRODUCT((MONTH('2012'!$A$3:$A$368)=8)*('2012'!$B$3:$B$368>0)),SUMPRODUCT((MONTH('2013'!$A$3:$A$367)=8)*('2013'!$B$3:$B$367>0),'2013'!$B$3:$B$367)/SUMPRODUCT((MONTH('2013'!$A$3:$A$367)=8)*('2013'!$B$3:$B$367>0)))

Thank you for all your help!!