Formula for Named Range to calculate Moving Average for charting

Hi!

- I have my data in a column for which I need to plot a 20 period moving average line using named range (I need a named range since I am using a no. of charts on real time basis. I am currently using helper columns to calculate 20 period moving average data which is greatly slowing my charts down).

- My oldest data point is at the bottom of the column and latest data is added at the top of the column.

- For my charting requirements, I need to display the axis on the right side of the chart, so that I need to keep my data plotted from right to left on the chart.

- In case I use the build-in Moving Average function of Excel, my moving average line starts at somewhere in the middle whereas it should start on the extreme right hand side of the chart. This is because, the built-in excel moving average functionality of Excel reads data points on the chart and the first point of the moving average line is plotted on the 20th data point on the chart.

If was wondering whether there can be a formula to plot a 20 period moving average line using Named Ranges that reads data from just my original data column.

Note: Sample excel sheet attached showing what should be an what I am getting

Not sure how to avoid a helper column. However, the following is a more efficient moving average formula:

Formula:

Please Login or Register  to view this content.

Fill down B22 to B98.

No working solution given here so request also posted at http://www.ozgrid.com/forum/showthre...518#post727518