double sort and percentrank function with NA in VBA

Rookieeee

New Member
Joined
Aug 26, 2019
Messages
22
Hello guys,

My data set is like this:


StockReturnCriteria1Criteria2Percentrank.INC
10.1W0.9
20.3M0.7
30.2NA0.3
40.1L0.5
50.5M0.33
60.2W0.88
70.1WNA
80.4L0.2
90.2L0.77

<tbody>
</tbody>

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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

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