Moving average using named range

[Kamarlon]

I saw Chandoo post on calculating a moving average. Does anyone know how to do it using named ranges. Additionally I want to forecast 1 period ahead using this method. How can this be done using named ranges. My actual data is in a named range "Y_Axis".

 

[Luke M]

Can you explain further your data layout? In Chandoo post: http://chandoo.org/wp/2009/04/28/calculate-moving-average/


he just calculated the average of cells that he knew the position of. Is your range dynamic, or is it always in the same place? Is the top left cell always the same? If so, you should be able to adapt the formula he gave like:

=AVERAGE(OFFSET(TopLeftCell,COUNT(NamedRange)-HowManyPtsToAverage,0,HowManyPtsToAverage,1))


I'm not sure how you want that applied to a forecast...

 

[Kamarlon]

Luke, that is teh post I am refering to.


The range I have is dynamic. The user select the KPI they want to view, so it's not going to be in the same column at all times.


Secondly say the named range "Y_Axis" runs from A1 to A30, then I want to forecast cell A31 as the average of the last three preriods A28:A30. Then A32 will be the average of A29:A31 and A33 will be the average of A30:A32 etc.


Hope I explained myself well.

 

[NARAYANK991]

Hi ,


Can you say whether my understanding is correct ?


You have data in various columns , e.g. A1:A30 , B3:B17 , C12:C42 ,...


Your named range can refer to any of these ranges.


When the named range refers to A1:A30 , you want the cell A31 to refer to the average of A28:A30 ,...


When the named range refers to B3:B17 , you want the cell B18 to refer to the average of B15:B17 ,...


When the named range refers to C12:C42 , you want the cell C43 to refer to the average of C40:C42 ,...


Narayan