1139Views10Replies

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
raishikoh

10 Replies

user
frollard (author)2013-02-15

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.

Select as Best AnswerUndo Best Answer

user
raishikoh (author)frollard2013-02-15

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

Select as Best AnswerUndo Best Answer

user
frollard (author)raishikoh2013-02-16

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.

Select as Best AnswerUndo Best Answer

user
raishikoh (author)frollard2013-02-15

I always struggle in explain things to people,
Sorry for the inconvenience....
Please see the following post:
http://www.mrexcel.com/forum/excel-questions/685935-run-visual-basic-applications-macro-code-loop-excel-2010-a.html
I am trying to do things automatically.... as its takes alot of my time...

Select as Best AnswerUndo Best Answer

user
frollard (author)raishikoh2013-02-16

You. Do. Not. Need. To. Use. VBA.

Select as Best AnswerUndo Best Answer

user
steveastrouk (author)2013-02-14

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

Select as Best AnswerUndo Best Answer

user
raishikoh (author)steveastrouk2013-02-14

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....

Select as Best AnswerUndo Best Answer

user
frollard (author)raishikoh2013-02-14

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
=PERCENTILE(Qarray,0.1)

Select as Best AnswerUndo Best Answer

user
raishikoh (author)2013-02-14

Please download the main file from the link given below

https://portal.bitcasa.com/send/e0964f0c22374a247c7145c0c12668f06e87d6c7cbe9cca5f720e1770b19a5e6/edb43c688d9fd616a6ebb5ee741afd2b5e0ab76b8d0047ad5181ee5c95e3a9a1

Select as Best AnswerUndo Best Answer

user
raishikoh (author)raishikoh2013-02-14

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

Select as Best AnswerUndo Best Answer