Weekly average formula (outputs only on last data entry of Sunday)

Please help!

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 the collected data during each shift 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!

I attached a copy of my worksheet. Column D is what I would like to have in the end and Column C is what I have so far.

Looks difficult. I'll take a swing at it.

So far, identifying last Sunday record for the week:

=IF(WEEKDAY(A2,1)=7,IF(WEEKDAY(A3,1)=1,999,""),"")

This notates a 999 on the last day of the week. Now just to substitute the 999 with a suitable average formula.

Sorry I'm kind of a noobie with Excel. Are you saying that I should use the average array function I have in Column C and put that in place of 999 in your formula? Thanks for helping!

That's right!

The final result in D2 would be:

=IF(WEEKDAY(A2,2)=7,IF(WEEKDAY(A3,2)=1,AVERAGE(IF(($A2-$A$2:$A2)<7,$B$2:$B2)),""),"")

(Still entered as an array)

Note, I changed the weekday function to (ref,2) for the Monday through Sunday layout.

Wow! Thank you so much! This is amazing