Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging last 8 weeks
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging last 8 weeks
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):offset(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 -- duan ----------------------------------------------------------------------- duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162 View this thread: http://www.excelforum.com/showthread.php?threadid=26903 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging last 8 weeks
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 -- duan ----------------------------------------------------------------------- duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162 View this thread: http://www.excelforum.com/showthread.php?threadid=26903 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Averaging last 8 weeks
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 "Montana" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for weeks | Excel Discussion (Misc queries) | |||
Date 13 Weeks from now | Excel Worksheet Functions | |||
after 2 weeks with the Beta | Excel Discussion (Misc queries) | |||
calculate weeks from a start date ( not yr weeks) | Excel Worksheet Functions | |||
Years and Weeks | Excel Programming |