I will lay out the question I currently have. I have an excel sheet in which column A is Date-type data, Column F is returns data, say returns of S&P500 (for examples sake). I have two cells, say G1 and G2, which have arbitrary start and end dates, respectively.
I want to be able to dynamically calculate the standard deviation of the returns data (Column F), with the start date and end date of the range of the standard deviation being equal to cells G1 and G2 respectively. Therefore, as I change cells G1 and G2, it will calculate a different standard deviation, as the range of the function will change.
I have already tried using an offset function, but have had difficulty in making the range dynamic in the sense that, if I just had a set start date, and was interested in changing the number of days to include in the range, I could do that.
The formula would look something like = STDEV.S(OFFSET(A4,1, 5, G3, 1)) where G3 is the number of days looking forward I want to include in the range.
This does not allow me to change the dates in interest as I please.
If I haven't explained something enough, please let me know.
Best,