Calculating dynamic moving averages
 

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


--
dmax007
------------------------------------------------------------------------
dmax007's Profile: http://www.excelforum.com/member.php...o&userid=36143
View this thread: http://www.excelforum.com/showthread...hreadid=559186

Calculating dynamic moving averages
 

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=559186

Calculating dynamic moving averages
 

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


--
dmax007
------------------------------------------------------------------------
dmax007's Profile: http://www.excelforum.com/member.php...o&userid=36143
View this thread: http://www.excelforum.com/showthread...hreadid=559186

Calculating dynamic moving averages
 

=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

"Dav" wrote in message
...

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


--
Dav
------------------------------------------------------------------------
Dav's Profile:
http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=559186