Rang reference incrementing by more than one on formula drag
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 |
Rang reference incrementing by more than one on formula drag
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 "Kai Cunningham" wrote: 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 |
Rang reference incrementing by more than one on formula drag
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 -- Biff Microsoft Excel MVP "Kai Cunningham" wrote in message ... 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 |
All times are GMT +1. The time now is 12:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com