Calculating average from every nth row across varying periods

Hello.
I have a large number of data for 1h measurements across 18 years in which I would like to calculate the hour average in each season. I am looking for a way to get the average of every nth hour (offset of 24h) across the seasonal scale (winter, spring, summer, fall) from a range of inputs (the period from 2000 to 2018).
Data selection for every nth hour for each day can be done using the OFFSET function + MATCH returns, but can I use the second-time OFFSET function to return a reference season using an accounting year approach? To return the quarter of the year, I can apply the formula of ROUNDUP(MONTH(…)/3,0) or IF condition where the first date actually starts of the season (e.g. winter) and the last date is the end of the season of the prior year, then move on to the next year by performing the twice OFFSET formula.
Finally, count all the cells will give the results of 24*4 rows (for 24 hours, e.g for 0:00, 1:00, 2:00 …. 22:00, 23:00; and 4 seasons) across the 2000-2018 time scale.
However, instead of making a complicated formula that would be likely to have errors in presented massive data I am looking for formula as a combination for matching criteria of next hour for dynamic season range calculations.

Attached I add test.xls
Thank you for help.

Originally Posted by GK

Some one will come up with a neater way... This is a monster.

Pivot? e.g. (with rogue break lines removed - presumably there to indicate lots of data in real-life)

Append the season to the lines: =LOOKUP(MONTH(A1),{1,3,6,9,12},{"Winter","Spring","Summer","Fall","Winter"})

Create a Pivot with Season as Column, and DateTime as Row - group latter by Hour (only), and set Values to Average.

Thank you for your answers.

In the original dataset, there are no breaks, I've just made them because of the huge size of document .xlsx (matrix size 168 000 rows x 214 columns). I have ongoing range from 1/1/2000 00:00 to 12/31/2018 23:00, however, some dates or values of 1h measurements are also lost.

Kind regards

Pivot approach attached.

Given your data is, technically, static in nature you could (if needed) remove the data tab once you've created the Pivot.
You can, in turn, regenerate the underlying data table by double clicking on the Grand Total in the Pivot itself.
This approach can be useful if the files become too large and you need to distribute etc.

I am grateful for the great solutions!

What I am thinking about how to properly compare my results because some of data range was lost. In the case of 1 h measurements, some cells were empty (no data) and
I want to check the data completeness (if the diurnal result includes 24 values - completeness = 100%; if seasonal sum of values, e.g. winter = 31+31+ 28 {Dec, Jan, Feb} * 24 = 2160 - completeness = 100%).

Thank you once again for your suggestions!

Hello,

I very much appreciate your guidance on this issue.

We are so close to having the correct formula for my specific data range. The missing thing is how to show the formula what to do when it encounters a blank cells. Many cells in the data range are blank and it is a trouble with getting the formula to ignore blank cells (not calculate as zeros).

=SUMPRODUCT((MONTH($A$2:$A$317)=IF(I2="Winter",{1,2,12},IF(I2="Spring",{3,4,5},IF(I2="Summer",{6,7,8},{9,10,11}))))*(ROUND(1440*MOD($A$2:$A$317,1),0)/1440=ROUND(1440*J2,0)/1440)*$B$2:$B$317)/SUMPRODUCT((MONTH($A$2:$A$317)=IF(I2="Winter",{1,2,12},IF(I2="Spring",{3,4,5},IF(I2="Summer",{6,7,8},{9,10,11}))))*(ROUND(1440*MOD($A$2:$A$317,1),0)/1440=ROUND(1440*J2,0)/1440))

I have reviewed other possibilities about this (e.g., IF($B$2:$B$317<>"", ... ) ; IF(NOT(ISBLANK($B$2:$B$317)), ... ) but still, have been unable to amend this long formula to calculate an average from every nth row across varying periods if cells are empty?

At present formula doesn't ignore Blank Cells when performing calculations and takes them as zeros...

Thanks in advance!