# Adding a fixed constant to a dynamically referenced array?

#### ctownballer03

##### New Member
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.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### ctownballer03

##### New Member
I'd think it would just be:

Book1
ABC
3376
3429
359933.13489
367633.22329
374133.07235
386932.57766
Sheet6
Cell Formulas
RangeFormula
C35:C38C35=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?

VBA Code:
``````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.

Last edited by a moderator:

#### Eric W

##### MrExcel MVP
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:

VBA Code:
``````              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:

VBA Code:
``MaxValue = 0``

to

VBA Code:
``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:

VBA Code:
``                           MaxDD = Cur DD``

I believe you meant

VBA Code:
``                           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

##### New Member
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

##### MrExcel MVP
Glad it works for you! Thanks for the update.

Replies
1
Views
147
Replies
3
Views
261
Replies
1
Views
113
Replies
0
Views
94
Replies
2
Views
147

1,127,612
Messages
5,625,839
Members
416,138
Latest member
Pizzaman22

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back