Is there a better way of doing this.
All this spreadsheet is doing is recording max and min temperatures per day over a period of months – this is part of a larger full year sheet that does more, this extraction is just to show the problem.
Columns A, B and C record the data.
Columns E, F and G, E gives the number of the month, F and E show if any data has been entered in data columns B and C, ‘1’ if yes ‘0’ if no.
Column I, the reference month. Data starting at month March
Column J, cell J3, the formula sums the ‘max’ data for March and divides by the number of days that have data to give an average, to do this the contents of cells E3, F3 and G3 are needed.
I’m assuming that anyone that can do it better will understand whats happening within the formula – because it would take some explaining.
The formula does to an extent work, but its hardly elegant and it does fail when a month has no data (as in July).
As it is its probably the extent of my capability but I would be interested in seeing a better way of doing it – if anyone has time to spare.