Calc problem, Is there a better way of doing this

[IGraham]

Is there a better way of doing this.

temperature3.ods

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.

[mariosv]

Find attached three ways to do with formula and with pivot tables.

Summarizing by months

There is the AVERAGEIF function so no need two SUMIF.

And the pivot tables can solve it easily even without break the date into other columns.
To get the resume by months, years, etc, use F12 on date column in the pivot table or with Menu/Data/Group and outline/Group

[IGraham]

Excellent, just what i was hopping for, all the methods are well beyond my comfort zone, think I’ll be doing a fair amount of head scratching. Thanks for taking the time, your work is appreciated

[IGraham]

i cant work out what ROW()-ROW(M$2) is doing, could you give a it more info please

[mariosv]

Calculate the month’s number automatically when the formula is copying down from January to December.

[IGraham]

thanks, a nit more work and thought needed on my part