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.
Bookmarks