Ask Your Question

Calc problem, Is there a better way of doing this.

asked 2018-06-17 10:55:23 +0200

IGraham gravatar image

updated 2018-06-17 11:23:36 +0200

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-06-17 17:06:11 +0200

m.a.riosv gravatar image

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

image description

edit flag offensive delete link more


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 gravatar imageIGraham ( 2018-06-19 00:11:04 +0200 )edit

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

IGraham gravatar imageIGraham ( 2018-06-19 00:13:10 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2018-06-19 01:01:46 +0200 )edit

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

IGraham gravatar imageIGraham ( 2018-06-19 09:56:01 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-06-17 10:55:23 +0200

Seen: 109 times

Last updated: Jun 17 '18