Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# how do i calculate averages based on dynamic date

Posted on 2011-09-08
Medium Priority
227 Views
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
Question by:JAK11
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
1 Comment

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 36507253
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.