Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating moving average | Excel Worksheet Functions | |||
Calculating weighted averages | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Any software for charting moving averages? | Excel Worksheet Functions | |||
calculating averages | Excel Discussion (Misc queries) |