title: Conditional percentiles in Excel VBA
body: <p>I'm trying find various percentiles of a range conditional on the data in a corresponding column being equal to a value. I've tried many different combinations, but I just keep getting different errors. This is where I am at so far:</p>
  
  <pre><code>Sub ConditionalPercentiles()
      'I need to write a procedure which find the percentile of column J conditional on the values in K
      Dim BK1, BK3, M3 As String
      Dim P99_7, P0_3 As Single
      Dim MyArray As Range
      Dim Data As Variant
      'Not sure if results and func need to be Dim-ed as variant
      Set Data = Range("J7:K1564")
  
      M3 = 3
      BK1 = "BK1"
      BK3 = "BK1"
  
      Application.ScreenUpdating = False
      With Data
          .AutoFilter Field:=11, Criteria1:=M3
          'This correctly finds the range I want
          MyArray = .DataBodyRange.SpecialCells(xlCellTypeVisible)
          'The problem is that the AutoFilter method above finds the necassary conditional area _
          'but I then have to capture that conditional range somehow in order to input it into _
          'the percentile function
          P99_7 = Application.WorksheetFunction.Percentile(MyArray, 0.997)
          'I only want to input the conditional data into the percentile calculation
      End With
      Application.ScreenUpdating = True
  End Sub
  </code></pre>