Hi helpful forum folks.
I have several years worth of daily weather data. The raw data includes the date (MM/DD/YYYY) and day of year (1-365, or 366 for a leap year).
I want to know the average value of a given measurement from all years for each day of the year.
I first tried to do a pivot table with the “day of year” as the row field, but that shows me average values for days 1-366. I don’t think this is accurate, as, on leap years, any day after 2/28 is off by one day.
I then used “date” as the row field, but I cannot figure out how to group them by year.
My ultimate goal is to get a table of monthly averages over all years from this data, but I think I need the table of daily averages first, as some measurements are daily totals (rainfall, for instance). So I need to get an average total for each day of the year, and then do a pivot table that summarizes this for each month.
Thank you in advance for the help!!