Daily Preciptation Data Select Help

[jessisavage]

Hello,

I have some daily data for precipitation and temperature from a weather station. There are 40 years of data, and I am interested in looking at the precipitation data for the months of May and June. Is there a way to select for these data? I will describe what I have tried below, and maybe somebody can help me with the next step, or let me know if I am on the right track.

- I tried copying and pasting just the data for May and June into another sheet. This would probably work, but it would take a long time to go through all 40 years. Is there a shortcut for this?

-The date is formatted like this: 5/1/1980. I tried sorting and conditional-formatting for cells that contain 5/1 etc. I want to be able to select the data every year from 5/1 to 6/30 for the 40 year time period. I could not think of a way to be more specific about what I wanted.

Thanks so much for the help!

Best,

Jess

[#2]

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.

[#3]

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!

[#4]

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))

[#5]

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?

[#6]

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.