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