7 day running average

redonthehd

New Member
Joined
Sep 17, 2014
Messages
3
Hi Everyone, I am new to the Group. I have an interesting formula problem. We have a spreadsheet and log for our 6 or 7 products we sell. there are 15 to 30 sales a day so each line is a sale. Column A is the date of the sale in format 9/8/2014, column B is the name of the customer, C the phone number, and D through L are product columns so Like D is Azteca and so on.

Now on separate tabs I have the running totals and graphs for each particular product. showing monthly sales but I want to have a running 7 day average of units sold for each product. I do this manually now with this formula <bdo dir="ltr">=SUM(MAIN!D771:D1153)/7

Each day I manually change the first cell number to reflect the date 6 days prior to the current date we are inputting.

I have searched and looked and unable to find a way to automate my manual changing of the current sales date minus 6days and put that in the formula to give me a 7 day running average. Any Ideas or Thoughts?
</bdo>
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi,
Like this, with 7 days?
datescustcellprod_1prod_2prod_3as of9/17/14
9/7/14452651prod_1 49.86
9/8/14459598prod_2 56.29
9/9/14983260prod_3 66.71
9/10/14323672
9/11/14446115
9/12/14804498
9/13/14126666
9/14/14641796
9/15/1455074
9/16/14719123
9/17/14736595

<!--StartFragment--> <colgroup><col width="65" span="9" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>
 

redonthehd

New Member
Joined
Sep 17, 2014
Messages
3
my spread sheet does not total each day it is per order:

9/1/2014 name 1
9/1/2014 name 1
9/1/2014 name 1
9/1/2014 name 1
9/1/2014 name 1
9/2/2014 name 1
9/2/2014 name 2
9/3/2014 name 1
9/3/2014 name 1
9/3/2014 name 1
9/3/2014 name 1

so the running average is the sum of those cells for that product for the lastest 7 day period / 7
 

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
try =AVERAGEIFS(D2:D200,A2:A200,">="&TODAY()-6,A2:A200,"<="&TODAY()) to average results from ColumnD, adjust range to fit you model.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,541
Messages
5,529,436
Members
409,877
Latest member
DDhol
Top