Need to optimize VBA code using array formula

[ikauai]

I have the following code run by a macro to populate a cell with the value of an array formula using the evaluate function. The formula works but it rather slow and I have several of them in a row that need to execute.

ActiveSheet.Range("O4").Value = Evaluate("MEDIAN(IF(E:E=M3, IF(I:I>0,I:I, 0)))")

Is there any way to make this run faster with an alteration or different procedure? Right now I have about 30 of them and it takes a couple of minutes to run, which is way too long for my purposes.

any help would be appreciated

 

[wigi]

As I already said in a couple of other topics today: use a pivot table for these kind of calculations. Do not use array formulas for these operations.

 

[ikauai]

Pivot tables cannot handle and do not provide the median formula (which I specified in my question) as an aggregate function. I have to resort to the array formula on the sheet or via vba. All other functions are fine such as average, sum, count etc but my report needs require median as well.

 

[Norie]

Perhaps you could alter the formula to use specific ranges rather than entire columns?

 

[ikauai]

Thanks, that did help a bit with the time to process. Needed to used a finalrow variable to set a dynamic range. Just getting back into this..

thanks for your help

 

[Norie]

I actually did a quick search on Median and pivot tables, and yes it isn't available as one of the standard aggregrate functions.

Howevert here was a lot of stuff about how to deal with the problem, workarounds, formulas, add-ins etc.

Mostly they were specific situations, or the examples were anyway, but it's probably worth taking a look.

You might get some ideas about code and/or the actual formula itself.

Actually, I was going to suggest you write your own UDF, but I'm not 100% sure what your formula is doing.

Looks a bit more complicated than a 'simple' median calculation.

Might even be an idea to ask here for help with the formula itself.

 

[wigi]

My bad, I thought the median was provided in a pivot table. Apologies.