Solved

how do i calculate averages based on dynamic date

Posted on 2011-09-08
1
219 Views
Last Modified: 2012-05-12
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
Comment
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
  • Learn & ask questions
1 Comment
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question