How to extract percentile from 2D array in Excel VBA?

Asked Jan 14 '21 at 15:21

Active Jan 14 '21 at 15:21

Viewed 19 times

I would like to extract the 80th percentile from the second column in MyArray below. Is this possible in Excel VBA?

I have tried with Worksheetfunction.Index below although this gives error "Argument not optional".

Sub test()

    Dim MyArray(1 To 2, 1 To 100)

    For f = 1 To 100
    
        MyArray(1, f) = f
        MyArray(2, f) = f * 3

    Next f

    Debug.Print Application.WorksheetFunction.Percentile(Application.WorksheetFunction.Index(MyArray, 0.80))

End Sub

Thank you!

asked Jan 14 '21 at 15:21

user2165379

You want to slice the array: see here for options and a discussion on which is the quickest: https://stackoverflow.com/questions/48598247/slice-array-to-use-index-on-larger-than-65000 – Scott Craner Jan 14 '21 at 15:23
@ Scott Craner That works for me. Great, thanks! – user2165379 Jan 14 '21 at 15:38
@ Scott Craner Shall I delete my question since my question has a similar question? – user2165379 Jan 14 '21 at 15:40
1

@user2165379 You're good to leave it here, it's been marked as a dupe so people who stumble upon this can follow the link. – Warcupine Jan 14 '21 at 15:42

How to extract percentile from 2D array in Excel VBA?

0 Answers0