Macro/UDF to calculate standard deviation of % changes

You can use worksheet functions is VBA like this:

<pre>
Dim rngIn As Range
Dim answer As Single

Set rngIn = Sheets("Sheet1").Range("A1:A5")
answer = Application.WorksheetFunction.StDev(rngIn)
MsgBox answer</pre>

I used data on Sheet1 A1:A5 to test.

HTH

If you are willing to use named formulas, this can be done without a UDF.

Create three names (Insert | Name > Define...)

aRng =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
FirstVals =OFFSET(aRng,0,0,ROWS(aRng)-1,1)
SecondVals =OFFSET(FirstVals,1,0)

where aRng picks up the data you have in col. A.

Now, array-enter in any cell (other than in col. A) the formula =STDEV((SecondVals-FirstVals)/FirstVals).

On a Wintel machine, to array-enter a formula, instead of ENTER to complete data entry, use CTRL-SHIFT-ENTER.

Mark and Tusharm, thanks for your suggestions.

Tusharm - I really do need a function/macro to do this but thanks anyway.

Mark the code you provided wont help my specific problem given that it can only be used after the % changes have been worked out in the spreadsheet.

I need the UDF/Macro to perform two tasks:

1) to work out the % changes from the raw data and store this as some kind of variable e.g X in the vb code (not spreadsheet)

2) and then work out the standard deviation of those percentage changes (X)

The previous UDF K provided works out the % changes and returns the AVERAGE of (X) but I need it to return the standard deviation instead.

Hope this is a little clearer.

Thanks,

Lucas

Use the foll. The common code allows you to add any other statistical analysis to your repertoire with almost no additional effort.

Lucas,

I was just posting to show you how to use a worksheet function as an example. I thought you would be able to incorporate it. Anyway, tusharm has given a nice example that will give you the functionality you desire. (In fact it's very nice, so I'm going to steal this one myself , thanks Tushar)

Tursham/Mark,

Thanks guys. This is exactly what I was looking for with extra flexability thrown in! Really useful.

Cheers,

Lucas

I know this is an old thread, but I just came across it in the Hall of Fame section. Tushar's code works great and I've stolen it for my own use.

I don't have very much experience using arrays, though, and I'm a confused about the "WorksheetFunction.Transpose" code.

Why is this line necessary, and in a related (I think) question, how can the code be adapted to work for data in rows as well as columns?

On 2002-10-01 18:08, MT_Shanachie wrote:
I know this is an old thread, but I just came across it in the Hall of Fame section. Tushar's code works great and I've stolen it for my own use.

I don't have very much experience using arrays, though, and I'm a confused about the "WorksheetFunction.Transpose" code.

Why is this line necessary, and in a related (I think) question, how can the code be adapted to work for data in rows as well as columns?

Click to expand...

Hi,

The transpose is necessary if the data is in a single column because the default for an array in VBA is a horizontal array.

Think of the elements of an array like

1, 2, 3, 4

rather than

1
2
3
4

If your data is in a column then to transfer the range into an array, you must transpose it from a vertical (column) array to a horizontal (row) array.

To adjust Tushar's code to handle the data in a row range, do the transpose twice.<pre>Option Explicit
Public fn As WorksheetFunction

Function computeDiff(rngIn As Range) As Variant
Set fn = Application.WorksheetFunction
'Assume rngIn is a single column _
If rngIn is a row, transpose twice _
If it is multi-dimensional, the problem is not well defined
Dim myArr, i As Long
myArr = fn.Transpose(fn.Transpose(rngIn))

For i = LBound(myArr) To UBound(myArr) - 1
myArr(i) = (myArr(i + 1) - myArr(i)) / myArr(i)
Next i
ReDim Preserve myArr(LBound(myArr) To UBound(myArr) - 1)
computeDiff = myArr
End Function
Function DiffStDev(rngIn As Range) As Variant
Set fn = Application.WorksheetFunction
DiffStDev = fn.StDev(computeDiff(rngIn))
End Function
Function DiffAvg(rngIn As Range) As Variant
Set fn = Application.WorksheetFunction
DiffAvg = fn.Average(computeDiff(rngIn))
End Function</pre>

You can also directly assign the range to an array, but by definition it will automatically be a multi-dimensional array.

_________________
Bye,
Jay

EDIT: A personal habit of mine is to assign an object variable fn to replace Application.WorksheetFunction. It is not necessary, so please don't think I've changed the functionality of Tushar's code in any way except to make it handle row ranges rather than columnar inputs.


EDIT2: The credit for assigning the object variable fn to replace Application.WorksheetFunction goes to Aaron Blood. Apologies for not giving due credit here originally.
This message was edited by Jay Petrulis on 2002-12-05 13:51

Thanks, Jay.

OK, so transposing twice enables the code to work with data in rows. Just to pursue this a little further, how can I adapt this code so that I can input data in either rows or columns (as I can with built-in functions)? I assume that I'll have to (as you said) assign the range directly to an array, but I don't know how to do that.

Alternatively, can I test to determine whether the data is in columns or rows and then transpose either once or twice as appropriate?

And by the way, why do I have to transpose twice (rather than not transposing at all) to get the code to work with data in rows?

Thanks in advance.