?
Solved

How do I calculate average based on a dynamic date

Posted on 2011-09-05
3
Medium Priority
?
192 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)
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
3 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 1000 total points
ID: 36486175
You can use AVERAGEIFS function, something like this

=AVERAGEIFS(Data!A2:Z2,Data!A$1:Z$1,">="&D$2-28,Data!A$1:Z$1,"<="&D$2-7)

where D2 is your date and in Data sheet row 1 has the dates and row 2 has your "Line 1" data. Repeat for line 2 (just drag formula down a line)

regards, barry
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36487893
Or you could use OFFSET to create the range to be averaged.

=AVERAGE(OFFSET(Ref,RowOffset,ColOffset,Height,Width))

Ref - first column of particular row
Rowoffset - Current row so 0
Coloffset - use Match to find column with date and less 3 for 4 weeks earlier.
Height - only one row so 1
Width - 4 weeks so 4

Thanks
Rob H
0
 

Author Comment

by:JAK11
ID: 36495409
This is great thanks.

If there are no data in the week columns how do i tell it to average the weeks with the data in. For eg if 11/07/11 didn't contain data i would want to take the 4 weeks average from the cells which do contain the data so this would take average 4 prev average from 04/07/11.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

764 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