Author Options:

VBA Code for finding the 10th percentile value for a certain bin Range? Answered

Hello everyone

I hope that you all are fine.

I have an excel sheet, and I am trying to bring some sort of automation in to that sheet (since I use it quite often) with the help of macros. The fact is that I am really new to macros and VBA programming language, so I need a little help in writing the code to find the 10th percentile for each bin value (50, 100, 150).
If you see the picture attached, the wanted percentile values are in Q, the bins for which I want to find the 10th percentile value, are given in Column M and the main data on which the 10 percentile value will be based is given in Column D.
By Using the data given in D and filtering out a certain bin width in column E (Example: Filtering bin width 50 or 100), we could find the Percentile value against each of the bin value given in Column N.

As said earlier I am new to VBA code, so any help will be appreciated in order to find the percentile values for each bin.
I can give the excel sheet as well if someone want to try hand on it, and let's me know the email address.

Additional Note: (The spread sheet is sorted out with respect to column E, is Ascending order (50, 100, 150) and the number of count for each bin width is given in column O, so you can just find the range of each bin by adding the count for each bin i.e range for first bin (50) is from 1 to 1+8542, similarly the range for the second bin (100) is given by 1+8542 to 1+8542+6862.

I will be very grateful if someone spares some time for me.

Kind Regards


Allow me to rephrase my previous...

WHAT exactly are you trying to calculate?

Could you not use filtering to create your bins?
Perhaps a pivot table to perform custom lists of calculations on your bins?
What exactly constitutes a bin?
As it stands, your values (by name) make almost no sense. Finding median values of a random assortment of unnamed values makes it very difficult to wrap my head around why you would want to solve for that number. No matter how I look at your question, I still don't see VBA being the answer.

it would so nice of you if you could help me solve this problem..

I say again.

I understand you want the different percentiles of different bins.

Use filter to FILTER the bins you want, then run a percentile calculation on a different tab that points to the different bins in the different filters.

I always struggle in explain things to people,
Sorry for the inconvenience....
Please see the following post:
I am trying to do things automatically.... as its takes alot of my time...

Am I missing something obvious here ? Where does programming come into it ? Don't you just need the correct cell formula ?

Well the formula for calculation the percentile is:
Percentile PERCENTILE( array, 0.1 )

But the problem is it applies to whole of the column, i need to calculate the percentile for a specific bin range, hence I have to select the part of array/ column that lies within the bin range... It need a code that calculates the percentile for each bin (specified in Column E). Although you are seeing all entries equal to 50 for column E in this pic, but if you scroll down the attached file then you will notice that the bin (Upper G) changes form 50 to 1400....

You can select a range and give it a name...

Select all the values you need for array, go to the name box, top left of your picture, currently has the value Q12 (will change when you type something in there). Call it something useful, like Qarray

Then, in your formula

Please download the main file from the link given below


if not clearly viewed just tab (twice or thrice) to select it all and the copy and download the main excel sheet