Complex Counting formula

[dharmalife87]

I need to count the average of all dates, in a given month, that return a value.
I have 10 years of data, each has a different spreadsheet, each has 2 columns that I need to deal with. 1st column is dates from Jan 1st to Dec 31st, the second column is inches of rain.

I need to get an average of the number of days it rains within a given month across a 10 year span.

 

[rilzniak]

IMO the best way to do it is to create a table for each year so you can have all data separate and then sum them together later if you wish. Here's what I came up with:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD]4/1/2014[/TD]
[TD]4/30/2014[/TD]
[TD]See Formula Below[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]5/1/2014[/TD]
[TD]5/31/2014[/TD]
[TD]See Formula Below[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD]6/1/2014[/TD]
[TD]6/30/2014[/TD]
[TD]See Formula Below[/TD]
[/TR]
</tbody>[/TABLE]

=AVERAGEIFS($B$2:$B$18,$A$2:$A$18,">="&E4,$A$2:$A$18,"<="&F4)

This assumes you'll be keeping the inches in column B and the dates in column A. I've only gone to A18 & B18 with the date and inches data but you can change the '18' to '18,000' in the formula (or a column reference of A:A & B:B but those are slower to calculate) and it will still work. The table I've created shown above is in the D1:G13 range so I've just entered the formula into G2 and copied down to average each month. Does this answer your question?

 

[dharmalife87]

I have the dates written in ##/##/#### format
I will need the formula to identify the month in number form on each table for each year.
All tables are 2 columns (inches in B and dates in A)

 

[dharmalife87]

I also just need to count the average times the value is over 0. I already have the average of inches per month

 

[rilzniak]

i have the dates written in ##/##/#### format
i will need the formula to identify the month in number form on each table for each year.
All tables are 2 columns (inches in b and dates in a)

=month(datevalue(d2&" 1")) if you're referring to the 'April', 'May', 'June' set of data or =month(e2) if you're referring to the mm/dd/yyyy format portion. This assumes the cells refer to the table I made above, in terms of references.

i also just need to count the average times the value is over 0. I already have the average of inches per month

=averageif($b$2:$b$18,">0")

 

[dharmalife87]

I'm sorry if I am not being clear on this
I do not need the average inches; I need the average number of days it rains per month over a 10 year span

Ex: August of 03 it rained 4 days
August of 04 it rained 6 days
August of 05 it rained 1 day
august of 06 it rained 3 days
Etc.

I need the answer to say 3.5 days

 

[thisoldman]

For one year, one formula for the number of rainy days in January is:

=SUMPRODUCT(--(MONTH('2003.xlsx'!$A$1:$A$365)=1),--('2003.xlsx'!$B$1:$B$365>0))

For February, it changes to:

=SUMPRODUCT(--(MONTH('2003.xlsx'!$A$1:$A$365)=2),--('2003.xlsx'!$B$1:$B$365>0))

To get the ten year average number of rainy days in January:

=AVERAGE(SUMPRODUCT(--(MONTH('2003.xlsx'!$A$1:$A$365)=1),--('2003.xlsx'!$B$1:$B$365>0)),
SUMPRODUCT(--(MONTH('2004.xlsx'!$A$1:$A$366)=1),--('2004.xlsx'!$B$1:$B$366>0)),
​ ​ <​--- similar lines deleted for brevity ---​>
SUMPRODUCT(--(MONTH('2011.xlsx'!$A$1:$A$365)=1),--('2011.xlsx'!$B$1:$B$365>0)),
SUMPRODUCT(--(MONTH('2012.xlsx'!$A$1:$A$366)=1),--('2012.xlsx'!$B$1:$B$366>0)))

Similar formulas will work for the other months.

 

[dharmalife87]

That did it! Thank You!

 

[thisoldman]

Glad to help. Thank you for lettting us know the problem was solved.