Standard deviation function

[bradyj7]

Hi there,

I'm trying to get the standard deviation of acceleration and deceleration of a range of cells. However, I keep getting the same result for both. I filter the cells in the range each time to display positive and negative values respectively. However, what can happen is that in the range the car might only be accelerating (or decelerating) the there will be no deceleration values (or acceleration values). In that case it should return the #DIV/0! error but it it is not.

Any ideas what is happening?

thanks

   ''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 acc
    End With

    
    StandardDevAcc = Application.StDev(Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)))
    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 deacc
    End With

    
    StandardDevDeacc = Application.StDev(Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)))
    Worksheets("kinematic").Activate
    ActiveCell.Offset(0, 17).Value = StandardDevDeacc

 

[baitmaster]

not sure about the ins and outs of the std dev function, its been a while since I did stats...

however, you are using autofilter to hide some cells, but I dont think you are ignoring them, programmatically

try adding .SpecialCells(xlCellTypeVisible) onto your range declarations

 

[p45cal]

baitmaster is right and the method works well:

StandardDevAcc = Application.StDev(Worksheets("raw").Range(Cells(sp, 7), Cells(ep, 7)).SpecialCells(xlCellTypeVisible))

There is another way:

Worksheets("kinematic").Activate
Set myOrigin = ActiveCell
With Worksheets("raw")
  .AutoFilterMode = False
  .Range("a1:j1").AutoFilter
  .Range("a1:j1").AutoFilter field:=7, Criteria1:=">0"  'only display cases of accel
  myOrigin.Offset(0, 16).Value = Application.Subtotal(107, .Range(.Cells(sp, 7), .Cells(ep, 7)))
  .Range("a1:j1").AutoFilter field:=7, Criteria1:="<0"  'only display cases of decel
  myOrigin.Offset(0, 17).Value = Application.Subtotal(107, .Range(.Cells(sp, 7), .Cells(ep, 7)))
End With

which uses SubTotal(107,range) to obtain the SD of the visible cells

 

[bradyj7]

Thanks guys, I'll try these out