Finding the Average of the Top 10% of a varying sized array

[excelbunny]

Hi All
I have a list of numbers in Excel which vary in length. I want to use some VBA coding to find the top 10% of the list and then return the average of the numbers that meet that criteria. I can do it very inelegantly by recording a macro that filters and then averages, but VBA wants to reference the list as absolute. I was wondering if there were an easier way that just uses VBA?

Many thnaks in advance

[dilipandey]

Hi excelbunny,

Welcome to the forum.

I guess you can use Large function to get the top 10% and then you can use Average function to get the desired results easily.... so finally it would be an array formula combining both of these functions.... Upload an sample file so that I can show it to you. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>