Averaging last 8 weeks

[Guest]

I am tracking sales information & need to figure out how to track the last 8
weeks average sales.
Each week I add new sales & would like to be able to see how this changes
the averages without having to manually reset the formulas to add the new
week and drop the 9th week.

Thanks in advance for any help.

 

[duane]

without seeing your sheet layout, one way to do this is to find th
current week data with a match function, and use the offset function t
back up 8 weeks to determine the range.

if current week date in a1, and assuming week dates in col b and dat
in col c in ascending weeks (most recent at bottom)


average(offset(c1,match(a1,b1:b1000,1)-1,0,1,1)ffset(c1,match(a1,b1:b1000,1)-8,0,1,1))

this averages the data from the latest date <= the date in cell a1 bac
8 weeks

this also assumes 1 data per week is in column

 

[duane]

another way without having to enter a date in cell a1 is


AVERAGE(OFFSET($C$1,COUNTA($B$1:$B$1000)-1,0,1,1):OFFSET($C$1,COUNTA($B$1:$B$1000)-8,0,1,1))

this assumes there is no data or dates below the last weeks dat

 

[ijb]

How is this data arranged? A few more details would help. Thanks

--
If I've mis-understood the question please tell me.

HTH

ijb

Remove nospam from my e-mail address to talk direct

Not MCSD, MVP, TLA, P&P, PCMCIA, etc just trying to help