Forum Discussion

jessisavage's avatar
jessisavage
Copper Contributor
Feb 24, 2019

Daily Preciptation Data Select Help

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

excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Feb 24, 2019

    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.

    • jessisavage's avatar
      jessisavage
      Copper Contributor
      Feb 24, 2019

      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!

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Feb 24, 2019

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

         

Resources