Forum Discussion
Daily Preciptation Data Select Help
Hi,
You may apply date filter. From drop-down for it select Month
After that type May and check Add current selection to filter. Ok
Repeat exactly the same for June. After that you shall have filtered May and June records for all years.
Excellent! Thank you very much for the quick reply. I have 2 follow-up questions.
Now, I am hoping to calculate the average precipitation for each month. I want to be able to select only the days of the month within the year that I want to calculate the precipitation average. I also want to know the number of days in the month that experienced precipitation. Maybe this could be inputted into a new table so that I can see the month/year and then the average precipitation and number of days of precipitation. Is there a way to do this quickly? Thank you!
If only to calculate some metrics you don't need to filter and/or copy your data. If, for example, your dates are in B column and precipitation is in column C, you may calculate total number of days in May and June for all years
=SUMPRODUCT(--(MONTH(B2:B20000)={5,6}))average precipitation for these months for all years
=SUMPRODUCT((MONTH(B2:B20000)={5,6})*C2:C20000)/SUMPRODUCT(--(MONTH(B2:B20000)={5,6}))For only one month (e.g. Septemeber) first formula will be
=SUMPRODUCT(--(MONTH(B2:B20000)=9))
Thank you! That is very helpful.
Do you know how to efficiently calculate the same metrics (number of days and average precipitation) for every May for all 40 years, every June for all 40 years, and for May and June together for all 40 years?
For example, May 1980 experienced 5 days of precipitation.
And, average precipitation for May 1984 was 3.45 cm
Is there a way to set up a macro so that the output for these calculations are organized into a table?
If you have both year and month you may use AVERAGEIFS. For example, for result data structured as
you may use in G2
=IFERROR(AVERAGEIFS($C$2:$C$3678,$B$2:$B$3678,">="&DATE($F2,COLUMN()-COLUMN($F$1),1),$B$2:$B$3678,"<="&EOMONTH(DATE($F2,COLUMN()-COLUMN($F$1),1),0)),"-")
and drag it down and to the right.
Please see the sample attached. In second sheet is the same for tables.