Moving Averages And the First Few Data Points

Hey Everyone,

I'm trying to create a Moving Average for price data. I want to be able to enter the number of data points I wish to use, rather than rewriting the average formula over and over again. I'm pretty close to my goal, however the first few data points return the "#REF!" error because the formula is trying to include the column title in the calculation.

I'm wondering if there's a way that if excel is told to use the 5 data points that it won't return the "#REF!" error, rather just the data points themselves ?, for example under the Short SMA if I request for excel to use 5 data points, I get 3 "#REF!", can I create a statement that says "If the formula Returns "#REF!" then Return today's Data Point", in other words do not do a calculation just give me today's price?

I have attached a sample sheet to clarify.

Hi ashleys,

See the attached file where I have corrected the formula for short sma, which can be replicated for other column as well. Let me know if face any challenge.

Regards,
DILIPandey

<click on below 'star' if this helps>

You are welcome ashleys...

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>