Rang reference incrementing by more than one on formula drag

[Kai Cunningham]

I am stumped on how to even approach the problem I have just encountered. I
have hourly temperature data for an entire year (8760 cells) in an excel
file, each corresponding to Month of year, Day of month, and Hour of day
cells in adjoining columns.

I would like to calculate the average temperature of each day based on the
max and min temperature hour of each day. The information I would like to use
exists in cells B6 through E8765,

The current formula I am using is

(MAX(E6:E29)+MIN(E6:E29))/2

to calculate the average temperature for day one. I have a list numbered 1
through 365 to record the average temperature calculation result. My problem
comes when I try to drag the formula to day 2, day 3, and so on. The new
range referenced is E7:E30 when I would like it to be E30:E53. I though that
the excel smart fill might get the hint if I entered the formula incrementing
as I wanted it to three times and then dragged the formula, as it does with
filling numbering, but it did not.
I'm not looking forward to editing this formula 362 more times to correct
the range referenced. I know there is a better way to set this up, but I'm
drawing a blank on the more advanced functions that may help. I tried making
the range a function of the day cell (E(Day4*24+6):E(day5*24+6)) to make the
increment jump by 24, but excel will not accept part of the cell reference
being a formula. Any input would be much appreciated!

TYIA,

Kai

 

[Bob Umlas, Excel MVP]

Enter this formula & fill down
=(MAX(OFFSET($E$6,(ROW(A1)-1)*24,0,24,1))+MIN(OFFSET($E$6,(ROW(A1)-1)*24,0,24,1)))/2

Bob Umlas
Excel MVP

 

[T. Valko]

Try this...

Enter this formula in G6:

=(MAX(OFFSET(E$6,(ROWS(G$6:G6)-1)*24,,24))+MIN(OFFSET(E$6,(ROWS(G$6:G6)-1)*24,,24)))/2

Copy down as needed. Each row will increment the calculation by 24 rows:

G6 will calculate the range E6:E29
G7 will calculate the range E30:E53
G8 will calculate the range E54:E77
etc
etc