Adding a fixed constant to a dynamically referenced array?

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.
 

Some videos you may like

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
Joined
Sep 7, 2017
Messages
4
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
Joined
Aug 18, 2015
Messages
10,754
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
Joined
Sep 7, 2017
Messages
4
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!
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top