Excel and/or Access ranking questions

G

Gromace

New Member
Joined
Jan 11, 2011
Messages
15
Thanks in advance for taking the time to read this!

I have a rather large data set, containing over 20,000 records and I am looking to calculate the percentile ranking of 10 to 20 different statistics for each record, in each category, if the statistic exists. Of these 20,000 records, I can break them into 30 different categories. The ranking is specific to the category, not to the entire data set.

On a smaller scale, the resulting sample data might look like this:

<table width="399" border="0" cellpadding="0" cellspacing="0"><col style="width: 16pt;" width="21"> <col style="width: 45pt;" width="60"> <col style="width: 29pt;" width="38"> <col style="width: 51pt;" width="68"> <col style="width: 29pt;" width="38"> <col style="width: 51pt;" width="68"> <col style="width: 29pt;" width="38"> <col style="width: 51pt;" width="68"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 16pt;" width="21" align="center" height="20">id</td> <td style="width: 45pt;" width="60" align="center">category
</td> <td style="width: 29pt;" width="38" align="center">Stat1
</td> <td style="width: 51pt;" width="68" align="center">Stat1Rank
</td> <td style="width: 29pt;" width="38" align="center"> Stat2</td> <td style="width: 51pt;" width="68" align="center"> Stat2Rank
</td> <td style="width: 29pt;" width="38" align="center">Stat3</td> <td style="width: 51pt;" width="68" align="center"> Stat3Rank</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">1</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">0</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">0</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">2</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">33</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">50</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">100</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">3</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">66</td> <td class="xl65" align="center">9</td> <td class="xl65" align="center">100</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">4</td> <td class="xl65" align="center">1</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">100</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">5</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">-2</td> <td class="xl65" align="center">100</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">0</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">6</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">-4</td> <td class="xl65" align="center">75</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">33</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">50</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">7</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">-6</td> <td class="xl65" align="center">50</td> <td class="xl65" align="center">9</td> <td class="xl65" align="center">66</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">100</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">8</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">-8</td> <td class="xl65" align="center">25</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">100</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">9</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">-10</td> <td class="xl65" align="center">0</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">10</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">2</td> <td class="xl65" align="center">0</td> <td class="xl65" align="center">-3</td> <td class="xl65" align="center">100</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">11</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">4</td> <td class="xl65" align="center">11</td> <td class="xl65" align="center">-6</td> <td class="xl65" align="center">75</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">33</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">12</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">6</td> <td class="xl65" align="center">22</td> <td class="xl65" align="center">-9</td> <td class="xl65" align="center">50</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">66</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">13</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">8</td> <td class="xl65" align="center">33</td> <td class="xl65" align="center">-12</td> <td class="xl65" align="center">25</td> <td class="xl65" align="center">16</td> <td class="xl65" align="center">100</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">14</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">10</td> <td class="xl65" align="center">44</td> <td class="xl65" align="center">-15</td> <td class="xl65" align="center">0</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">15</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">12</td> <td class="xl65" align="center">55</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">16</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">14</td> <td class="xl65" align="center">66</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">17</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">16</td> <td class="xl65" align="center">77</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">18</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">18</td> <td class="xl65" align="center">88</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">19</td> <td class="xl65" align="center">3</td> <td class="xl65" align="center">20</td> <td class="xl65" align="center">100</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> <td class="xl65" align="center">
</td> </tr> </tbody></table>
I am trying to automate the calculation of the Stat1Rank, Stat2Rank and Stat3Rank columns. The first problem I am having in automating this task is that the number of records in each category changes every month. I want to make sure I am calculating the Percent Rank on the entire category without having to edit the formula each month. Also, if a record does not contain a value in the Stat1 column, I don't want it included in the ranking calculation in the Stat1Rank column.

Is there a way I can define my range in the Percentile Rank Function as all records in Category 1 that have an entry in the Stat1 column?

Would this task be possible or better in Access?

Any help would be greatly appreciated!

Thank You
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
hi - welcome to the board.

my solution to this was going to be to use an formula array approach to generate the array for the percentrank. something like:

=PERCENTRANK(IF($B$2:$B$11=B2,IF(LEN($C$2:$C$11)>0,$C$2:$C$11)),C2)

(i.e. assumes the 'record does not contain a value in the stat1 column' = cell is empty).


<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=256 border=0><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.333</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.666</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.25</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.5</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>0.75</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

...is that what you're after?
 
Upvote 0
PaddyD,

Thanks for your response! I did try your suggestion, but I got slightly different results. If the field is empty, I am getting a rank of 1, or as I would read it 100%. If there is no data, I don't want there to be a rank at all. It also should not be included in the "count" for calculating the percentile rank.


Here are the results I got for your sample data:

<table width="192" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" align="center" height="20">Cat</td> <td style="width: 48pt;" width="64" align="center">Stat1</td> <td style="width: 48pt;" width="64" align="center">Stat1Rank</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">1</td> <td align="center">1</td> <td align="center">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">1</td> <td align="center">2</td> <td align="center">0.25</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">1</td> <td align="center">3</td> <td align="center">0.5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">1</td> <td align="center">
</td> <td align="center">1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">1</td> <td align="center">5</td> <td align="center">0.875</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">2</td> <td align="center">1</td> <td align="center">0</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">2</td> <td align="center">2</td> <td align="center">0.25</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">2</td> <td align="center">3</td> <td align="center">0.5</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">2</td> <td align="center">4</td> <td align="center">0.75</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">2</td> <td align="center">5</td> <td align="center">0.875</td> </tr> </tbody></table>
Here is the formula:

=PERCENTRANK(IF($B$2:$B$11=B2,IF(LEN($C$2:$C$11)>0,$C$2:$C$11)),C2)

Cat is in column B
Stat1 is in column C
Stat1Rank is in Column D

I am confused by the 0.875 result. It makes me think the blank is being counted in order to calculate the percentile. The way I think, if I was to rank 100 items, the worst one would be on the bottom, with a rank of 1 and the best one would be on the top with a rank of 100. How would that work if there were only 4 items? The worst would be 25 and the best would be 100??? Makes sense if you think of quartiles, but it seems strange to think of the bottom of the pack as 25 instead of 0. I really don't like the fact that the zeros come into play and will probably round them up to 1 in my real data, once I get there. If it would help to see the real data I am looking to rank, I would be happy to share it, but it consists of 22600 records. I could email it perhaps? Is there a better way?

Back to my results, am I missing something or do I have a typo in my formula? I haven't had much time to look into this further, but I will dig deeper over the weekend. I am posting this response just to let you know you where I currently stand. Thanks again for your time!

I'll keep you posted
 
Upvote 0
sorry - I should have been more explicit. You need to enter the formula with control + shift + enter, not just enter.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 26.25pt" height=35><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 26.25pt; BACKGROUND-COLOR: #efefef" width=64 height=35>Cat

</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>Stat1</TD><TD class=xl69 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>Stat1Rank</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>1</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>1</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>1</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>2</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>0.333</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>1</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>3</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>0.666</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>1</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64> </TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>1</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>5</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>1</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>2</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>0.25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>3</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>0.5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>2</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>4</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>0.75</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #efefef" width=64 height=21>2</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #efefef" width=64>5</TD><TD class=xl70 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=64>1</TD></TR></TBODY></TABLE>
 
Upvote 0
please try
Code: 
Sub test()
 
  Const lng_COL_CATEGORY As Long = 2    'the category data is in column 2 of the worksheet
 
  Dim i As Long, j As Long, k As Long
  Dim lngCalcModeBefore As Long
  Dim lngThisColumn As Long
  Dim lngRowLast As Long
  Dim lngRowCategoryFirst As Long
  Dim lngRowCategoryLast As Long
  Dim ar As Variant
  Dim arColsToPopulate As Variant
 
  Application.ScreenUpdating = False
  lngCalcModeBefore = Application.Calculation
  Application.Calculation = xlCalculationManual
 
  'this array contains a list of the columns to be populated with formulas
  arColsToPopulate = Array(4, 6, 8)    'the values being ranked are in the columns before these
 
  'populate an array with the worksheet data (data assumed to be already sorted on category)
 
  With Range("A1").CurrentRegion
    ReDim ar(1 To .Rows.Count, 1 To .Columns.Count)
    ar = .Value
    lngRowLast = .Rows.Count
  End With
 
  'ensure columns to be populated are already empty
  For i = LBound(arColsToPopulate) To UBound(arColsToPopulate)
    Cells(2, arColsToPopulate(i)).Resize(lngRowLast).Clear
  Next i
 
  'loop through all the categories. Note, reusing variable i as counter
  i = 2
  Do Until i >= lngRowLast
    'the worksheet row with the first record for a category
    lngRowCategoryFirst = i
 
    'find the last worksheet row for the category
    Do Until ar(i, lng_COL_CATEGORY) <> ar(i + 1, lng_COL_CATEGORY) Or i = lngRowLast - 1
      i = i + 1
    Loop
    If i = lngRowLast - 1 Then i = i + 1
    lngRowCategoryLast = i
    i = i + 1
    'so, the data for this category is from worksheet row lngRowCategoryFirst to lngRowCategoryLast
 
    'loop through each column to receive formulas
    For j = LBound(arColsToPopulate) To UBound(arColsToPopulate)
      lngThisColumn = arColsToPopulate(j)
      'find last row to load with a formula
      For k = lngRowCategoryLast To lngRowCategoryFirst Step -1
        If Len(ar(k, lngThisColumn - 1)) > 0 Then
          Cells(lngRowCategoryFirst, lngThisColumn).Resize(k - lngRowCategoryFirst + 1).FormulaR1C1 = "=PERCENTRANK(R" & lngRowCategoryFirst & "C" & lngThisColumn - 1 & ":R" & k & "C" & lngThisColumn - 1 & ",RC[-1])"
          Exit For
        End If
      Next k
    Next j
  Loop
  Application.Calculation = lngCalcModeBefore
End Sub
 
Upvote 0
PaddyD,

Thanks again for your attention to this matter. I tried using the Control+Shift+Enter and it did work much better, however I am still having an issue with the records without an entry in the stat column. When I used the formula on my real data, the records without an entry received a very low rank, but not the lowest in the group. My data has 2069 records in category 1. 15 of them do not contain any value in the stat1 column. Those 15 got a rank of 0.025, or 2.5%. There were 52 records that scored below 2.5%, not including the "blanks".

After reviewing the other categories, I noticed that the ranking of the blanks is directly related the the number of records who had a stat larger than Zero. A stat of any number above, below or equal to Zero is possible, but a blank should not be ranked. In the case of category 1, approximately 97.5% of the records were >= to 0. In Category 2, there are 167 records and 149 had stats above Zero. 8 were below Zero and 10 had nothing in the field (blanks). The blanks were ranked 4.5%

I would be happy to share my results, but I am not sure how to do that here. Would email be better? Are there any other options?

Thanks again PaddyD!

Fazza - I am looking at your suggestion now and will share my results and or questions. I recognize your code as visual basic, I think, but I do not have a ton of experience with it. I'll keep you posted and thanks for the attention.

Gromace
 
Upvote 0
Fazza,

I tried your macro on the sample data and it appeared to work fine. However, when I tried to use it on the real data it produced the following error.

Run time error "9":
Subscript out of range

If I hit the end button, it brings me back to the worksheet, but correctly populates the stat1 column, for category 1 only. If I run the macro again, it produces the same error, but if I hit end, the stat1 rank column is still populated (or populated again) and the stat2 rank column is now populated as well. This second stat rank column however has a rank for the blanks. If I continue to run the macro, it produces the same results, populating a new column each time.

I would be happy to share the file I am working with, but not sure the best way to do it. Please let me know if that would help.

Here is a sample:

<table width="514" border="0" cellpadding="0" cellspacing="0"><col style="width: 53pt;" width="71"> <col style="width: 86pt;" width="114"> <col style="width: 73pt;" width="97"> <col style="width: 48pt;" width="64"> <col style="width: 78pt;" width="104"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 53pt;" width="71" align="center" height="17">ID Number</td> <td style="width: 86pt;" width="114" align="center">Category Number</td> <td style="width: 73pt;" width="97" align="center">Stat1
</td> <td style="width: 48pt;" width="64" align="center">Stat1Rank
</td> <td style="width: 78pt;" width="104" align="center">Stat2
</td> <td style="width: 48pt;" width="64" align="center">Stat2Rank
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">1</td> <td align="center">1</td> <td align="center">17.09</td> <td align="center">1</td> <td align="center">38.87</td> <td align="center">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">2</td> <td align="center">1</td> <td align="center">16.92</td> <td align="center">0.999</td> <td align="center">38.29</td> <td align="center">0.999</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">3</td> <td align="center">1</td> <td align="center">16.78</td> <td align="center">0.999</td> <td align="center">21.16</td> <td align="center">0.992</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">4</td> <td align="center">1</td> <td align="center">16.77</td> <td align="center">0.998</td> <td align="center">37.49</td> <td align="center">0.998</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">5</td> <td align="center">1</td> <td align="center">16.67</td> <td align="center">0.998</td> <td align="center">37.37</td> <td align="center">0.998</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">6</td> <td align="center">1</td> <td align="center">15.65</td> <td align="center">0.997</td> <td align="center">
</td> <td align="center">0.012</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">7</td> <td align="center">1</td> <td align="center">15.64</td> <td align="center">0.997</td> <td align="center">
</td> <td align="center">0.012</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">8</td> <td align="center">1</td> <td align="center">15.62</td> <td align="center">0.996</td> <td align="center">
</td> <td align="center">0.012</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="center" height="17">9</td> <td align="center">1</td> <td align="center">15.39</td> <td align="center">0.996</td> <td align="center">
</td> <td align="center">0.012
</td> </tr> </tbody></table>
Category 1 has 2069 records. 2054 records have a value for stat1 and the blanks did not get ranked for the stat 1 column. 1890 have a value for stat2 and all the blanks rec'd a ranking of 0.012. The value of 0.012 has to do with where the zero falls in the dataset. In other words, a value of Zero is better than 98.8% of all the records with an entry.

If I hit the Debug button, it highlights the following line:

If Len(ar(k, lngThisColumn - 1)) > 0 Then

Any thoughts would be appreciated.

Thanks!
 
Upvote 0
Hi. been away, so no idea if this is still live, but in any case...

re the blanks. rather than:

=PERCENTRANK(IF($B$2:$B$11=B2,IF(LEN($C$2:$C$11)>0,$C$2:$C$11)),C2)

try (untested):

=PERCENTRANK(IF(LEN($B$2:$B$11)>0,IF($B$2:$B$11=B2,IF(LEN($C$2:$C$11)>0,$C$2:$C$11))),C2)
 
Upvote 0
Sorry, Gromace, I only just saw your latest post.

When I test, it works OK for me. I'm guessing we have a different set up.

Are the headers in row 1 & the data immediately under from row 2? If you can post sample data using one of the tools referred to in the posting guidelines/help (or sticky threads?) it would help: these seem to include row & column info.

This fractionally different code does the same on my test data as the earlier code: so although it works on mine, I'm expecting it will (like the earlier code) NOT work correctly for you.

Code: 
Sub test_2()
  '
  Const lng_COL_CATEGORY As Long = 2    'the category data is in column 2 of the worksheet
  '
  Dim i As Long, j As Long, k As Long
  Dim lngCalcModeBefore As Long
  Dim lngThisDataColumn As Long
  Dim lngRowLast As Long
  Dim lngRowCategoryFirst As Long
  Dim lngRowCategoryLast As Long
  Dim ar As Variant
  Dim arColsToPopulate As Variant
  '
  Application.ScreenUpdating = False
  lngCalcModeBefore = Application.Calculation
  Application.Calculation = xlCalculationManual
  '
  'this array contains a list of the columns to be populated with formulas
  arColsToPopulate = Array(4, 6, 8)    'the values being ranked are in the columns before these
  '
  'populate an array with the worksheet data (data assumed to be already sorted on category)
  'it is also assumed the data headers are in row 1 & the data immediately under (from row 2)
  With Range("A1").CurrentRegion
    ReDim ar(1 To .Rows.Count, 1 To .Columns.Count)
    ar = .Value
    lngRowLast = .Rows.Count
  End With
  '
  'clear the worksheet columns that are to be populated with formulas
  For i = LBound(arColsToPopulate) To UBound(arColsToPopulate)
    Cells(2, arColsToPopulate(i)).Resize(lngRowLast).ClearContents
  Next i
  '
  'loop through all the categories. Note, reusing variable i as counter
  i = 2
  Do Until i >= lngRowLast
    'the worksheet row with the first record for a category
    lngRowCategoryFirst = i
    '
    'find the last worksheet row for the category
    Do Until ar(i, lng_COL_CATEGORY) <> ar(i + 1, lng_COL_CATEGORY) Or i = lngRowLast - 1
      i = i + 1
    Loop
    '
    If i = lngRowLast - 1 Then i = i + 1
    lngRowCategoryLast = i
    i = i + 1
    'so, the data for this category is from worksheet row lngRowCategoryFirst to lngRowCategoryLast
    '
    'loop through each column to receive formulas
    For j = LBound(arColsToPopulate) To UBound(arColsToPopulate)
      lngThisDataColumn = arColsToPopulate(j) - 1    'the data being ranked is in the previous column
      k = lngRowCategoryLast
      Do Until Len(ar(k, lngThisDataColumn)) > 0
        k = k - 1
      Loop
      Cells(lngRowCategoryFirst, arColsToPopulate(j)).Resize(k - lngRowCategoryFirst + 1).FormulaR1C1 = "=PERCENTRANK(R" & lngRowCategoryFirst & "C" & lngThisDataColumn & ":R" & k & "C" & lngThisDataColumn & ",RC[-1])"
    Next j
  Loop
  '
  Application.Calculation = lngCalcModeBefore
  '
End Sub

I will have another look when the data set up is confirmed.

regards
 
Upvote 0
PaddyD,

Thanks again for your help, but I was unable to make your formula work for me. It did rank column C, but it did not take into account the different categories. In other words, all 19 were ranked as one group.

To be fair, I didn't spend too much time working with it because I have made more progress with Fazza's solution. Perhaps I can work your soultions into his code??? We'll see...

Thanks again!

Fazza,

Just saw your post, must have missed the notification email? I have made progress with your first set of code, but was still getting ranks for the blank fields. I'll take a look at your latest code and post more specific results. Also, I'll look at the posting guidelines/ help as well to see the best way to show my results.

Thank you both!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
478
mumps
mumps
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
347
dilshod_k
D
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
901
epactheactor
E

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top