Standard deviation error

[bradyj7]

Hi there,

I'm calculating the standard deviation of deceleration of a vehicle every 30 seconds as it travels along. I do this by filtering out cells with positive acceleration. However sometimes no deceleration occurs in that 30 second time period and hence there are no negative values in that range of cells. And vice versa when calculating the standard deviation of acceleration.

To prevent the macro stopping when there are no cells in the range I used "On error resume next" so it will continue and get the standard deviation of the next 30 second block.

However, when no deceleration occurs in a 30 second block, I was expecting excel to return an error in the cell or leave it blank. But instead it returns the standard deviation of the previous 30 seconds which I don;t want it to do.

Why is it doing this? How can I make it leave it blank or return "No values" and continue on? Or even just the error #DIV/0! and continue on?

Thank you

 'Standard deviation Acceleration

    Worksheets("raw").Select
    With ActiveSheet

            .AutoFilterMode = False
            .Range("a1:j1").AutoFilter
            .Range("a1:j1").AutoFilter field:=7, Criteria1:=">0"  'only display cases of acceleration
    End With

    StandardDevAcc = Application.StDev(Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)).SpecialCells(xlCellTypeVisible))
    On Error Resume Next
    Worksheets("kinematic").Activate
    ActiveCell.Offset(0, 16).Value = StandardDevAcc
    
    'Standard deviation Deccceleration

    Worksheets("raw").Select
    With ActiveSheet

            .AutoFilterMode = False
            .Range("a1:j1").AutoFilter
            .Range("a1:j1").AutoFilter field:=7, Criteria1:="<0"  'only display cases of deacceleration
    End With

    
    StandardDevDeacc = Application.StDev(Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)).SpecialCells(xlCellTypeVisible))
    On Error Resume Next
    Worksheets("kinematic").Activate
    ActiveCell.Offset(0, 17).Value = StandardDevDeacc

 

[Jon Peltier]

After you successfully retrieve the StandardDevDeacc, you should then change it to some error value. Otherwise the code remembers the previous value, since it has not been overwritten.

 

[bradyj7]

Thanks for the help