7 day running average

[redonthehd]

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>

 

[cyrilbrd]

Hi,
Like this, with 7 days?

dates	cust	cell	prod_1	prod_2	prod_3		as of	9/17/14
9/7/14			45	26	51		prod_1	49.86
9/8/14			45	95	98		prod_2	56.29
9/9/14			98	32	60		prod_3	66.71
9/10/14			32	36	72
9/11/14			44	61	15
9/12/14			80	44	98
9/13/14			12	66	66
9/14/14			64	17	96
9/15/14			5	50	74
9/16/14			71	91	23
9/17/14			73	65	95

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

 

[redonthehd]

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]

try =AVERAGEIFS(D2:D200,A2:A200,">="&TODAY()-6,A2:A200,"<="&TODAY()) to average results from ColumnD, adjust range to fit you model.

 

[redonthehd]

THank YOU

 

[cyrilbrd]

THank YOU

Most welcome...