double sort and percentrank function with NA in VBA

[Rookieeee]

Hello guys,

My data set is like this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Stock[/TD]
[TD]Return[/TD]
[TD]Criteria1[/TD]
[TD]Criteria2[/TD]
[TD]Percentrank.INC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.1[/TD]
[TD]W[/TD]
[TD]0.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.3[/TD]
[TD]M[/TD]
[TD]0.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0.2[/TD]
[TD]NA[/TD]
[TD]0.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0.1[/TD]
[TD]L[/TD]
[TD]0.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0.5[/TD]
[TD]M[/TD]
[TD]0.33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0.2[/TD]
[TD]W[/TD]
[TD]0.88[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]0.1[/TD]
[TD]W[/TD]
[TD]NA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0.4[/TD]
[TD]L[/TD]
[TD]0.2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]0.2[/TD]
[TD]L[/TD]
[TD]0.77[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I want to do is to run double sorts in VBA. The sample here is cross-sectional, but my data also has time dimension. What I need to calculate is the percentrank.INC of each stock. Lets take stock 1 as an instance, this stock belongs to group W, and its value of criteria 2 is 0.9. I want to calculate the percentranl.Inc of its criteria 2 within the W group. (We also have NA in both criteria1,2 columns)

As what I mentioned, I also need to repeat this step over a long period. Therefore, I prefer to using VBA code for this scenario.

Thank you guys so much.

 

[jasonb75]

If you want automatic calculation of the results then having a preference of vba over native worksheet functions could be a false economy. If your reason for wanting vba is slow response when entering data then I would suggest formulas in the worksheet with a button to toggle between manual and automatic calculation.

It would be benificial to add the results that you expect to the example, along with a bit more information about the expected handling of the NA entries. Should all NA's in criteria1 be treated as a group the same as W, M or L would be, or ranked individually (high or low). Should the W in criteria1 with NA in criteria 2 be considered as a third W stock or do we only look at the 2 with a number in criteria2? If it should be included as part of the ranking, how should it compare to the other stocks in the same group?