Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a reference to calculate moving average
I have a column that calculates a moving average of the values in the
preceding column and would like to be able to use a reference cell embedded in the moving average formula so that I can alter the reference cell, say from 8 to 10, and the formula will adjust from an 8-day moving average to a 10-day moving average. Anyone know how to do this? Your help is greatly appreciated! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a reference to calculate moving average
One way:
Name your reference cell, say, "days". Then B10: =AVERAGE(OFFSET(A10,1-days,,days,1)) In article , jtujague wrote: I have a column that calculates a moving average of the values in the preceding column and would like to be able to use a reference cell embedded in the moving average formula so that I can alter the reference cell, say from 8 to 10, and the formula will adjust from an 8-day moving average to a 10-day moving average. Anyone know how to do this? Your help is greatly appreciated! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a reference to calculate moving average
Thank you!! I had not come across the OFFSET function yet. Big help. Happy
Holidays! "JE McGimpsey" wrote: One way: Name your reference cell, say, "days". Then B10: =AVERAGE(OFFSET(A10,1-days,,days,1)) In article , jtujague wrote: I have a column that calculates a moving average of the values in the preceding column and would like to be able to use a reference cell embedded in the moving average formula so that I can alter the reference cell, say from 8 to 10, and the formula will adjust from an 8-day moving average to a 10-day moving average. Anyone know how to do this? Your help is greatly appreciated! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using a reference to calculate moving average
Hi,
Here is another variation: =AVERAGE(OFFSET(A1,,,F$1)) Where F1 contains the number of days you want in your moving average and A1 is the first cell to average. You can shorten it to =AVERAGE(OFFSET(A1,,,N)) If you range name cell F1 N. Here are two longer ways =AVERAGE(INDIRECT("A"&ROW()&":A"&ROW()+F$1-1)) or =AVERAGEIFS(D,D,"="&ROW(),D,"<="&ROW()+F$1-1) (in 2007 where D is the range name of the entire range you want to use the running average with.) -- If this helps, please click the Yes button Cheers, Shane Devenshire "jtujague" wrote: I have a column that calculates a moving average of the values in the preceding column and would like to be able to use a reference cell embedded in the moving average formula so that I can alter the reference cell, say from 8 to 10, and the formula will adjust from an 8-day moving average to a 10-day moving average. Anyone know how to do this? Your help is greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
moving average | Excel Discussion (Misc queries) | |||
Moving Average | Excel Worksheet Functions | |||
Calculate a 30-day moving average based on the last x number of entries and date | Excel Worksheet Functions | |||
how to do a 50 day and 200 day moving average | Excel Discussion (Misc queries) | |||
Moving average | Charts and Charting in Excel |