Hello all - new to this forum but not new to Excel - I work in the environmental engineering consulting industry.
I have a need to calculate an average of a monthly value (say rainfall) for each month of the year over multiple years while accounting for leap years. The partially processed data look something like this where a monthly average value is calculated from a large table assuming the daily values are either from a non-leap year or from a leap year (one extra day in February occurring every 4th year). I don't have the luxury to reformat the data.
Year Month NonLeap Leap
1 Jan 3.0 3.5
2 Jan 2.0 2.5
3 Jan 4.0 4.2
4 Jan 2.5 2.5
5 Jan 1.5 1.6
6 Jan 3.8 3.7
7 Jan 4.1 4.2
8 Jan 3.3 3.5
For this example, the average of the bold values would calculated.
The example spreadsheet has data for each month for 8 years and the desired outcome.
TIA
Bookmarks