x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 236

# 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
0
JAK11
1 Solution

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.