Help getting average daily value from multiple years worth of daily data

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.

File here for reference.

Thank you in advance for the help!!

2 Likes

Thank you!!!