How can I code a 10th percentile macro in Excel?

rai shikoh February 14, 2013
Pinterest Stumbleupon Whatsapp

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.

The link for the email is: http://www.instructables.com/files/deriv/FXG/ORUY/HD4VOBHD/FXGORUYHD4VOBHD.LARGE.jpg

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

  1. Bruce Epper
    February 15, 2013 at 3:09 am

    Okay, I just spent the last 90 minutes completely overthinking the problem. A simple solution requires that you do a couple of things to prep your data. First, sort the data on 2 columns, the bin (column E) and your data (column D) in ascending order for both columns. Next, create named ranges in column D for each of the bins. Now, in column Q use the following formula: =INDEX(Bin50, INT(COUNT(Bin50) * .1) + 1. You need to replace the 'Bin50' in the formula for each bin caclulation with the appropriate named range you created in column D for the proper bin.

    This entire thing can also be done without the named ranges but the formula becomes fairly complex and it will greatly increase the calculation time.

  2. Bruce Epper
    February 15, 2013 at 1:49 am

    For all bins with fewer than 8192 elements, you can use the built-in statistics functions to do this. It is easier if you use named ranges for this, then in the desired cell you can simply use =PERCENTILE(Bin50, 0.1) to find the 10th percentile of the specified named range (Bin50).