WorksheetFunction.Percentile - Array Formula

Hello there,

I'm trying to get Percentile Formula within VBA in order to get a percentile by item and date. The excel Formula is the following:

{=Percentile(IF(($A$2:$A$100=$D2)*($B:$B=E$1),$D:$D,""),0.9)}

I've Tried the obvious with application.worksheetfunction.percentile but, obviously without sucess.

Can you help in this one?

Thanks in advance.

Fernando Costa

Hello GC Excel,

Thanks for the reply.

I've already came up with the code up above, the thing is that it takes a huge amount of time since it is an array formula. I need to calculate about 100 percentil per day, and the average time, of one day report takes about 6minutes. In some days, i've have 10 report to calculate, and one hour is way more than i can spare with it. In the other hand, the file gets to heavy to work with array formulas, usually i add the code after the array formula in order to get free of those nasty calculations:

Please Login or Register  to view this content.

What i'm actually looking for is a way to calculate those results without a formula being inserted, which would take much less time.

Best Regards,

FJcosta