Adding a fixed constant to a dynamically referenced array?

[ctownballer03]

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.

 

[Eric W]

I'd think it would just be:

Book1
	A	B	C
33	76
34	29
35	99		33.13489
36	76		33.22329
37	41		33.07235
38	69		32.57766
Sheet6

Cell Formulas
Range	Formula
C35:C38	C35	=STDEV.S(0,A1:A35)

 

[ctownballer03]

I'd think it would just be:

Book1
	A	B	C
33	76
34	29
35	99		33.13489
36	76		33.22329
37	41		33.07235
38	69		32.57766
Sheet6

Cell Formulas
Range	Formula
C35:C38	C35	=STDEV.S(0,A1:A35)

Shoot, the above works for the example I provided, but I unforunately provided an overly simplified example that doesn't scale to my actual problem.

Apologies for this and thanks for your answer to the above, but let me reframe my exact issue very precisely. I'm using a custom-built function with VBA and the input variable is a range, and unfortunately input ranges in this custom built function don't behave like the input range in pre-canned functions such as stdev I've learned as I've already tried the above zero insertion method for this function and it doesn't work correctly like it does in your example with stdev formula.

I'm no expert in VBA but I pieced together the following code for a Max Drawdown formula off a series of returns from the internet a while back, but subsequently realized the formula doesn't work appropriately unless the first data point in the range is a positive number or zero, but due to the reasons mentioned in the initial post in regards to dragging the formula for a rolling max drawdown calculation, I'm trying to find a way to always input a zero at the start of this range ahead of the actual range of cells referenced in the spreadsheet. Any idea how I could adjust the below VBA code to incorporate a zero at the beginning of every selected range?

Function MaxDrawdown(MyArray As Range)

Dim CurValue As Double
Dim MaxValue as Double
Dim MyCell As Range
Dim CurDD as Double
Dim Max DD as Double

MaxValue = 0
MaxDD = 0
CurValue = 1000
For Each MyCell In MyArray
               CurValue = CurValue * (1 + MyCell)
               If CurValue > MaxValue Then
               MaxValue = CurValue
               Else
               CurDD = (CurValue / MaxValue - 1)
                            If CurDD < MaxDD Then
                            MaxDD = Cur DD
                            End If
               End If
Next My Cell
MaxDrawdown = MaxDD
End Function

So looking for a way to add a zero at the beginning of the MyArray in the above. Thanks again for any help! Really appreciate it and apologizes again for my oversight in the initial example provided.

 

[Eric W]

I don't especially understand what your macro is trying to accomplish, but If you want to add the equivalent of a 0 to the start of your array, you'd just need to run your loop 1 extra time at the beginning with 0 as the value. Looking at your loop, that would mean that these 3 lines would get executed:

              CurValue = CurValue * (1 + MyCell)
               If CurValue > MaxValue Then
               MaxValue = CurValue

If the value you're assuming for MyCell is 0, then CurValue would be set to 1000, and MaxValue would also be set to 1000. So functionally, if you want to perform the equivalent of adding a cell with 0 to the top of your range, you'd just need to change this line:

MaxValue = 0

to

MaxValue = 1000

Again, I don't know what your loop is trying to do, so I don't know if that makes sense mathematically (and I have some doubts), but it works from a programming point of view.

Also, look at this line:

                           MaxDD = Cur DD

I believe you meant

                           MaxDD = CurDD

with no space. Using the Option Explicit parameter at the top of your module can help you find typos like that.

Good luck!

 

[ctownballer03]

Wow this is amazing. Just spent 30 mins checking different results and can't find any issues. The formula takes investment returns and calculates the maximum drawdown over a defined period of time.

Thank you so much!

 

[Eric W]

Glad it works for you! Thanks for the update.