Creating Custom Function: TRIMSTDEV

  • Thread starter Thread starter Norvin Laudon
  • Start date Start date
N

Norvin Laudon

Hi,

I need to create a custom function similar to TRIMMEAN, but instead of
computing an average, it will compute a standard deviation. You pass it a
range, and a "percent" value to ignore at either end of the range.

I'll list the steps the function needs to perform:

1. Sort the passed range
2. Remove the lowest "percent" cells, and the highest "percent" cells from
the population.
3. Compute the STDEV on the remaining cells.

Questions:

How can I modify the range (i.e. sort it, remove cells, etc,) without
modifying the actual cells?
I could create an array from the range and modify that, but I think I still
need to convert it back to a range to use the STDEV function...

Any tips?

Thanks,
Norvin
 
Thanks Tom,

I missed that functionality...

So another question: My function below works fine when called in VB, but
does not work when called from Excel as function in a cell. It seems the
range passed to the function is readonly when used from Excel, which means
my .Sort function won't work.

Any ideas on how to do this? Can I create a range that is disconnected from
cells & use that to do my calculations?

Norvin

'code *******************************************************************
Public Function TrimSigma(theRange As Range, percent As Single) As Double

Dim backup() As Variant, tmpRange As Range, cellsToIgnore As Long

backup = theRange.Value 'create a backup of the range

theRange.Sort theRange(1, 1), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'this calculates the number of cells to ignore at the start
'and end of the range (for a 20 cell range, with percent = 0.1,
'this value would be 2)
cellsToIgnore = CLng(percent * Range(theRange(1, 1), theRange(1,
1).End(xlDown)).Count)

'calculate the stdev of the reduced data set
TrimSigma = WorksheetFunction.StDev(Range(theRange(cellsToIgnore +
1).AddressLocal + ":" + theRange(Selection.Count -
cellsToIgnore).AddressLocal))

theRange.Value = backup 'restore from backup

End Function

'************************************************************************
 
A function can't affect the excel environment - it can't change values in
other cells, can't do formatting, etc. It can only return a value to a
cell. I will locate my array sort function and post it in a few minutes.
 
This appears to work (lightly tested):

Public Function TrimSigma(theRange As Range, percent As Single) As Double

Dim i As Long, j As Long
Dim tmpRange As Range
Dim cellsToIgnore As Long
Dim varr As Variant

varr = theRange.Value
QuickSort2 varr, 1, LBound(varr, 1), UBound(varr, 1)
'this calculates the number of cells to ignore at the start
'and end of the range (for a 20 cell range, with percent = 0.1,
'this value would be 2)
cellsToIgnore = CLng(percent * (UBound(varr, 1) - _
LBound(varr, 1) + 1))
j = UBound(varr, 1)
For i = LBound(varr, 1) To LBound(varr, 1) _
+ cellsToIgnore - 1
varr(i, 1) = False
varr(j, 1) = False
j = j - 1
Next
'calculate the stdev of the reduced data set
TrimSigma = WorksheetFunction.StDev( _
varr)


End Function




Sub QuickSort2(SortArray, col, L, R)
'Originally Posted by Jim Rech 10/20/98 Excel.Programming
'Modified to sort on first column of a two dimensional array
'Modified to handle a a second dimension greater than 1 (or zero)
Dim i, j, X, Y, mm

i = L
j = R
X = SortArray((L + R) / 2, col)

While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j > L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
If (L < j) Then Call QuickSort2(SortArray, col, L, j)
If (i < R) Then Call QuickSort2(SortArray, col, i, R)
End Sub
 
Tom,

That's great, thanks alot!

The key for me:
varr(i, 1) = False
Click to expand...

I didn't know this could be done... It's way better than creating a new
array to hold the reduced dataset...

Cheers,
Norvin
 
Back
Top