How to get Stdev of Range?

[warlordusa]

Hello all,

Below is apart of my code where I take a range of data, and try to find the avg, max, min, and now stdev. I am getting values for my avg max min, but nothing for my std, specifically this line of code:

Std = Application.WorksheetFunction.StDev_S(MyRange)

Any help would be appreciated.
Thanks




Code here:


Dim CurrentColumn As Integer
CurrentColumn = 5 '=5 because results start on column E

Dim LastColumn As Integer

'Find the last used Column
LastColumn = Sheet1.Cells(7, Columns.Count).End(xlToLeft).Column

'Loop to get all of the results and formats in each column

Do While CurrentColumn <= LastColumn

Dim MyRange As Range
Dim Avg As Double
Dim Min As Double
Dim Max As Double
Dim Std As Double
Dim AvgPlusStd As Double
Dim AvgMinStd As Double



Set MyRange = Range(Cells(7, CurrentColumn), Cells(LastDataRow, CurrentColumn))

'Put Average, Max, and Min In Table
'And now Stdev and +/- 3 Dev

On Error Resume Next ' This line ignores an error and goes to the next line of code. This is usefull when trying to take the

Avg = Application.WorksheetFunction.Average(MyRange)
Min = Application.WorksheetFunction.Min(MyRange)
Max = Application.WorksheetFunction.Max(MyRange)
Std = Application.WorksheetFunction.StDev_S(MyRange)
AvgPlusStd = Avg + (3 * Std)
AvgMinStd = Avg - (3 * Std)

If AvgMinStd < 0 Then
AvgMinStd = 0
End If


Sheet1.Cells(AvgRow, CurrentColumn).Value = Avg
Sheet1.Cells(MinRow, CurrentColumn).Value = Min
Sheet1.Cells(MaxRow, CurrentColumn).Value = Max

 

[Domenic]

Maybe MyRange contains one or more error values?

If this is the case, StDev will return an error.

However, the error would be ignored, since you have On Error Resume Next. And std would remain unchanged with either its initialized value, that being 0, or previously assigned value.

First, I would suggest removing On Error Resume Next from your code.

Secondly, declare std as Variant instead of Long. This will allow it to store either a Double or error value.

Thirdly, use the std method of the Application object, instead of the WorksheetFunction object...

Std = Application.StDev_S(MyRange)

This allows for a non-breaking error, for which you can test...

If Not IsError(Std) Then
    'do something
Else
    'do something else
End If

Hope this helps!

 

[Domenic]

Some further suggestions...

1) You should declare all your variables. You can place the Option Explicit statement at the top of your module, which will force all variables to be declared, and help avoid possible errors (and headaches).

2) You should qualify all your range references, otherwise they'll refer to the active sheet instead of the intended sheet. So, for example, if the active sheet is a chart sheet instead of a worksheet, you'll get an error. So you can avoid this by doing it this way...

'Find the last used Column
LastColumn = Sheet1.Cells(7, Sheet1.Columns.Count).End(xlToLeft).Column

and

With Sheet1
    Set MyRange = .Range(.Cells(7, CurrentColumn), .Cells(LastDataRow, CurrentColumn))
End With

Notice the dots ( . ) referring to Sheet1.

Hope this helps!