[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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
Asked:
JAK11
1 Solution
 
barry houdiniCommented:
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now