Calculating dynamic moving averages

[dmax007]

Im a novice so forgive me if this is a basic question. I am working o
a sales tracking spreadsheet that will be updated daily. I would lik
to display a 12 week average, 6 week average, and 2 week average. (
have no problems creating the spread sheet by manually typing th
formulas - My goal is to have the sheet update the formulas when
update the date and sales.) Can anyone offer a solution. I a
thinking that I could include a cell that would have the days dat
(which I would update at the same time as updating the daily sales).
The write a formula that would use that date minus 84 days for 12 week
minus - 42 days, - 14 days. This would give me the start date and en
date to define my range. Then ask for an average of the cells tha
fall within the range. Below is a simplified table layout: An
help would be greatly appreciated.

Date Fred George David Tim
1-03-06 $XXX $XXX $XXX $XXXX
1-04-06 $XXX $XXX $XXX $XXXX
1-05-06 $XXX $XXX $XXX $XXXX
1-06-06 $XXX $XXX $XXX $XXX

 

[Dav]

Im a novice so forgive me if this is a basic question. I am working on a
sales tracking spreadsheet that will be updated daily. I would like to
display a 12 week average, 6 week average, and 2 week average. (I have
no problems creating the spread sheet by manually typing the formulas -
My goal is to have the sheet update the formulas when I update the date
and sales.) Can anyone offer a solution. I am thinking that I could
include a cell that would have the days date (which I would update at
the same time as updating the daily sales). The write a formula that
would use that date minus 84 days for 12 week, minus - 42 days, - 14
days. This would give me the start date and end date to define my
range. Then ask for an average of the cells that fall within the range.
Below is a simplified table layout: Any help would be greatly
appreciated.

Date Fred George David Tim
1-03-06 $XXX $XXX $XXX $XXXX
1-04-06 $XXX $XXX $XXX $XXXX
1-05-06 $XXX $XXX $XXX $XXXX
1-06-06 $XXX $XXX $XXX $XXXX


If you put the date you are interested in in cell g1, and your dates
are in column A, fred in B etc

For Fred and 12weeks

=sumproduct(($a$1:$a$1000>$g$1-84)*($a$1:$a$1000<=$g$1)*(b$1:B$1000))/84

the can be copied to the right for george david tim

then for the other ranges just change the 84 to the appropriate number
of days

If it is always to today g1 can be replaced with today() or today -1 as
the result will probably only be up to yeserday

Regards

Dav

 

[dmax007]

Dav, Thanks for taking the time to respond. I tried the formula yo
posted. First I tried it on the actual sheet that I needed it for. I
did not work so I made a mach sheet - very simple - still no luck -- i
returns the value - "Value" in the calculated feild. ???? any othe
suggestions?? Thanks

 

[Biff]

=sumproduct(($a$1:$a$1000>$g$1-84)*($a$1:$a$1000<=$g$1)*(b$1:B$1000))/84

Dividing by 84 is not a good idea.

There may not be 84 entries. Just use AVERAGE to find the average between
the date ranges.

F1 = start date
G1 = F1-84

Array entered using the key combinationof CTRL,SHIFT,ENTER:

=AVERAGE(IF((A1:A20>=G1)*(A1:A20<=F1),B1:B20))

Biff