ctownballer03
New Member
- Joined
- Sep 7, 2017
- Messages
- 4
Is it possible to add a constant to an array? Imagine I have monthly numerical data in cells A1:A100 and let's say I want an easily draggable formula that returns 3 year standard deviation in cell B36 that I can the drag to B100 to get a time series of rolling 3 year standard deviation information on my data. Now if for whatever reason I wanted the data to always start with a value of zero in the calculation, is there a way to add a zero into a dynamic array without actually having a zero in a cell that the formula is referencing.
Specifically I would like to write this formula in B35 such that it calculates standard deviation on data that looks like: (0;A1-A35). So then I could get 3 year standard deviation on the last 35 months of data and 0, and drag that down all the way to B100 to calculate this rolling standard deviation on last 35 months of data and 0.
I spent a shockingly large amount of time trying to get this to work without success so curious if anyone is aware of a way to do this (ideally w/o VBA).
Thanks for any help here and let me know if my question isn't clear in any way.
Specifically I would like to write this formula in B35 such that it calculates standard deviation on data that looks like: (0;A1-A35). So then I could get 3 year standard deviation on the last 35 months of data and 0, and drag that down all the way to B100 to calculate this rolling standard deviation on last 35 months of data and 0.
I spent a shockingly large amount of time trying to get this to work without success so curious if anyone is aware of a way to do this (ideally w/o VBA).
Thanks for any help here and let me know if my question isn't clear in any way.