I have Column A (Date/time) which is always shown in 1/1/13 7:00:00 format. The only times are 7:00:00 and 19:00:00 (day and night shifts). Column B has collected data and column C is the average for the data. Currently I have the average (col C) with an array function of =AVERAGE(IF(($A2-$A$2:$A2)<7,$B$2:$B2)). When filled down, every row has an average value from the previous 7 days. I only want to have the average taken on the last Sunday 7pm entry from the previous Monday's first 7 am entry. I also want to highlight these values in Column C. Can anyone help? I'm a little lost!
Edit: I attached a copy of my worksheet. I highlighted the first 3 average outputs I would like to show (as mentioned, it is always from the first Data entry of Monday 7 am shifts to the last entry on Sundays 7 pm.
Bookmarks