I have a problem with finding the suitable formula for finding the moving average. I can only use the functions "average", "index", and "row" to find the Nth day moving average. I cannot use anything such as "offset" or simply use the data analysis tool. That means, for example, if my original nth value of days were 4, and if I change it to 5, excel has to automatically change all the values of the moving average in terms of 5 days from 4 days. Also, I do not want excel to calculate the blank spaces. Every time I try calculating, it adds the blank cells and treats the blank cells as the value 0. I want to exclude all the blank spaces so that if there are not enough days to calculate the moving average, the cell will show #N/A or something similar instead of calculating the average while including the blank cells.
stock0.xlsx
I have attached a file. I need to find the moving average for the cell E (The one that says "close"). I do not know what to do so I just used the "average" function. But that won't change the values of "N-MA" if I change my nth value. Since I do not know how to do it, I just manually typed in #N/A.
Please help. Thanks.
Bookmarks