how do i calculate averages based on dynamic date

i have a spreadsheet with the following layout.

       06/06/2011      13/06/2011      20/06/2011      27/06/2011      04/07/2011      11/07/2011
Line 1      114      200      980      879      675      600
Line 2      78      50      333      89      32      23
Total      2281      2294      2147      1906      2254      2043

On a new worksheet I want to be able to calculate the past 4 weeks average for each line,
 based on the date the user enters.

Eg if I enter 11/07/2011 in the below cell I want the average to be calculated for Line 1 and Line 2.

11/07/2011
Line 1      (fomula here to calc avg of 04/07/2011 to 13/06/2011)
Line 2      (fomula here to calc avg of 04/07/2011 to 13/06/2011)
 
I've used the below formula which works great.  But i need to incorporprate null entries into this formula.  eg if there was no data for week 04/07/11 then i want to use the next available date which has data in so it would be avg of 27/06/11 to 06/06/11. how can i incorporate this in the below formula.
 
=AVERAGEIFS(Data!A2:Z2,Data!A$1:Z$1,">="&D$2-28,Data!A$1:Z$1,"<="&D$2-7)

thanks
JAK11Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
barry houdiniConnect With a Mentor Commented:
I think this should do it......

=AVERAGEIFS(Data!A2:Z2,Data!A$1:Z$1,">="&MIN(D$2-7,LOOKUP(9.99E+307,Data!A2:Z2,Data!A$1:Z$1))-21,Data!A$1:Z$1,"<="&MIN(D$2-7,LOOKUP(9.99E+307,Data!A2:Z2,Data!A$1:Z$1)))

regards, barry
0
All Courses

From novice to tech pro — start learning today.