Calculate Percentile on Filtered Data

[rjplante]

I have a large table with many columns. I would like to filter the table first using VBA and then calculate the percentile of a single column using VBA to store the data in a different workbook. I have cobbled together how I think it should work using other sections of my code, I just need help with the percentile calculation of the filtered data.

I appreciate all the help.

'   Total P90 DATA
 
Dim FLTR_SET_1 As Integer
Dim PRCNTL_SET_1 As Integer
Dim lastrow As Long
Dim rng As Range
 
'   Define last row of data
    Sheets("Work Tasks").Select
    Range("A300000").Select
    Selection.End(xlUp).Select
    lastrow = Selection.Row
 
Set rng = Sheets("Work Tasks").Range("A2:A" & lastrow)
 
'   if autofilter is on, show all the data
    Sheets("Work Tasks").Select
    If ActiveSheet.AutoFilterMode Then
        Range("A2:BA2").Select
        Selection.AutoFilter
        Selection.AutoFilter
    End If
 
'   Select Investigation Tasks worksheet
    ActiveWorkbook.Worksheets("Work tasks").Select
   
'   Clear all data filters
    ActiveWorkbook.Worksheets("Work Tasks").AutoFilter.Sort.SortFields.Clear
   
'   Filter on search for tools 1, 3, and 5 under (Column T=20)
    ActiveSheet.Range("$A$2:$BA" & lastrow).AutoFilter Field:=20, Criteria1:=Array( _
        "tool 1", "tool 3", "tool 5"), Operator:=xlFilterValues
 
'   Capture Inventory Age (Column L=12)
    FLTR_SET_1 = rng.Columns(12).SpecialCells(xlCellTypeVisible).Count - 1
   
    If FLTR_SET_1  > 0 Then
       
           PRCNTL_SET_1 = Percentile(FLTR_SET_1,0.9)
       
    '   Transfer Percentile data over to tracking workbook
        ThisWorkbook.Sheets("Data Tracking").Range("D3").Value = PRCNTL_SET_1
 
    Else
   
    '   Enter count of default value of 0
        ThisWorkbook.Sheets("Data Tracking").Range("D3").Value = 0
    End If