CODEWITHSUNDEEP
excelvbastdev
Lelung
Lelung

Reputation: 11

Standard deviation calculation where sometimes there is no data

I want cell W4 to be the standard deviation of range C to V.

Some of the rows will be empty and thus not able to calculate a standard deviation.

I would like to return a blank cell or better to delete the row.

For i = 1 to 1000
    Range("W4").Cells(i, 1) = Application.WorksheetFunction.IfError(WorksheetFunction.StDev(Range(Range("C4").Cells(i, 1), Range("V4").Cells(i, 1))), "")
Next i

Upvotes: 0

Views: 161

Answers (1)

Storax
Storax

Reputation: 12167

With the following code the cells in column W will be untouched in case Application.WorksheetFunction.IfError(WorksheetFunction.StDev(Range(Range("C4").Cells(i, 1), Range("V4").Cells(i, 1))), "") runs into an error.

On Error Resume Next
Range("W4").Cells(i, 1) = Application.WorksheetFunction.IfError(WorksheetFunction.StDev(Range(Range("C4").Cells(i, 1), Range("V4").Cells(i, 1))), "")
On Error GoTo 0

If you want to make sure that the cell in column W will be blank you could use the following code

On Error Resume Next
    Range("W4").Cells(i, 1) = Application.WorksheetFunction.IfError(WorksheetFunction.StDev(Range(Range("C4").Cells(i, 1), Range("V4").Cells(i, 1))), "")
    lsterr = Err.Number
    On Error GoTo 0
    If lsterr > 0 Then
        Range("W4").Cells(i, 1).Value = ""
    End If

And if you want to delete the complete row you have to adjust the loop as well like that

For i = 1000 To 1 Step -1
    On Error Resume Next
    Range("W4").Cells(i, 1) = Application.WorksheetFunction.IfError(WorksheetFunction.StDev(Range(Range("C4").Cells(i, 1), Range("V4").Cells(i, 1))), "")
    lsterr = Err.Number
    On Error GoTo 0
    If lsterr > 0 Then
        Range("W4").Cells(i, 1).EntireRow.Delete
    End If
Next i

Upvotes: 0

Related Questions