Macro/UDF to calculate standard deviation of % changes

[Lucas in London]

Hi all,

This relates tp a query I raised a few weeks ago. Thanks to Kkknie and Jay for the very usefulUDFs they provided.

I have a seris/column of numerical data for which I want to work out percentage differences and then calculate the standard deviation of the percentage differences for all values

For example in column A, I have

row 1 10
row 2 15
row 3 17
row 4 10

I want to work out % difference between row 1 and row 2 (which is 50%), row 2 and row 3 etc and the get the standard deviation of thoose % changes.


I know how to do this on the spreadsheet using formulas but I want to do it using a macro so that the macro returns the standard deviation of the percentage changes because I do not want to have any data showing on the spreadsheet other than the raw data.

kkknie kindly provided the following UDF to work out the the average of the percentage changes but is there code that will work out the standard deviation instead?

I thought maybe excel's built-in standard deviation function (stdev)could be incorporated somewhere into the code but I'm not sure how!

Function AvgPct(rngIn As Range)
Dim dblErrAccum As Double
Dim intErrCount As Long
Dim dblLastVal As Double
Dim r as range
dblErrAccum = 0
dblLastVal = 0
intErrCount = 0

For Each r In rngIn
intErrCount = intErrCount + 1
If intErrCount > 1 Then
'dblErrAccum = dblErrAccum + (r.Value - dblLastVal) / dblLastVal
dblErrAccum = dblErrAccum + Abs((r.Value - dblLastVal)) / dblLastVal
End If
dblLastVal = r.Value
Next

AvgPct = dblErrAccum / (intErrCount -1)

End Function

Hope you can help me. Thanks

Hament

 

[Mark O'Brien]

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

 

[tusharm]

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.

 

[Lucas in London]

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

 

[tusharm]

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

Option Explicit
    Function computeDiff(rngIn As Range) As Variant
        'Assume rngIn is a single column _
         If rngIn is a row, don't do the transpose _
         If it is multi-dimensional, the problem is not well defined
        Dim myArr As Variant, i As Long
        myArr = Application.WorksheetFunction.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
    DiffStDev = Application.WorksheetFunction.StDev(computeDiff(rngIn))
    End Function
Function DiffAvg(rngIn As Range) As Variant
    DiffAvg = Application.WorksheetFunction.Average(computeDiff(rngIn))
    End Function

 

[Mark O'Brien]

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)

 

[Lucas in London]

Tursham/Mark,

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

Cheers,

Lucas

 

[MT_Shanachie]

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?

 

[Jay Petrulis]

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?

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

 

[MT_Shanachie]

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.