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

 

[Jay Petrulis]

Hi,

If you assign rngIn to myarr directly as in

myarr = rngIn

or

myarr = rngIn.Value

you will get a two dimensional array automatically, and the rest of the computation will need to adjust for the second dimension of the array.

The transpose is a technique to insure a one dimensional array and the double transpose is the way I thought of to handle the rows vs. columns data structure. There may be better ways to do this, but I don't know them.

Here is the routine adapted to handle data in rows or columns, but not both. It returns and error for multi-dimensional data sets and returns zero for a single cell, else it returns as expected.

<pre>Option Explicit
Public fn As WorksheetFunction

Function computeDiff(rngIn As Range) As Variant
Dim myArr, i As Long
Set fn = Application.WorksheetFunction

With rngIn
If .Rows.Count > 1 And .Columns.Count > 1 Then
computeDiff = CVErr(xlErrValue)
Exit Function
End If

If .Rows.Count = 1 And .Columns.Count = 1 Then
computeDiff = 0
Exit Function
End If

If .Rows.Count > 1 Then
myArr = fn.Transpose(rngIn)
Else
myArr = fn.Transpose(fn.Transpose(rngIn))
End If
End With


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</pre>

The other functions are unchanged.

 

[MT_Shanachie]

Thanks again, Jay.

I was just tinkering around with using the row and column count on the range. I hadn't thought to include the error handling in cases of a multi-dimensional range, so I'll add that.

I appreciate you taking the time to help me work through this.

 

[gupta_deep]

Hi,

I know it is TOOOOOO late to jump in this. I have a array formula for this problem. I know the querier wants an macro code, but it might be a work around

=STDEV((B3:B5-B2:B4)/B2:B4)

You need to press CTRL + SHIFT + ENTER after typing the formulae.

It assumes the data is in cells B2 to B5 and the formula is inserted in cell C6, you can change the references accordingly.

Rgds,
SK

 

[mortgageman]

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

I realize I'm going way OT here, but what is the advantage of using the
object variable in this case?