'Percentrank' function under double sorts with 'NA's

Rookieeee

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

I have 2 columns, the first one (we say A1:A100) contains 'W's, 'M's, 'L's, and some 'NA's, the second column (B1:B100) contains figures (stock returns) and also some 'NA's. what I want to do is to calculate the percent rank for each stock under 'W' group, 'M' group, and 'L' group respectively, ignoring 'NA's.

Basically, the first sort is 'W', 'M', and 'L'. For example, under 'W' group, we have some stocks, and I want to calculate the percent rank of stock i in 'W' group. Same calculation happens in 'M', and 'L'.

Thank you guys so much.

Cheers!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi Rookieeee,

I'll assume the "NA" really is just text and not the result of a failed lookup "#N/A".

I wasn't sure what you meant by "percent rank" so I'm showing percent in one column and rank in another; you can delete the redundant column.

ABCDEFGH
1StockReturnsStockTotal ReturnsPercentage of TotalRank
2W33W15437.75%1
3M44M12129.66%3
4L55L13332.60%2
5NA999
6W88
7M33
8L1
9NA999
10W33
11M44
12L77

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
F2=SUMIFS($B$2:$B$100,$A$2:$A$100,E2)
G2=F2/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")
H2=RANK.EQ(F2,$F$2:$F$4,0)
F3=SUMIFS($B$2:$B$100,$A$2:$A$100,E3)
G3=F3/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")
H3=RANK.EQ(F3,$F$2:$F$4,0)
F4=SUMIFS($B$2:$B$100,$A$2:$A$100,E4)
G4=F4/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")
H4=RANK.EQ(F4,$F$2:$F$4,0)

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Hi Rookieeee,

I'll assume the "NA" really is just text and not the result of a failed lookup "#N/A".

I wasn't sure what you meant by "percent rank" so I'm showing percent in one column and rank in another; you can delete the redundant column.

ABCDEFGH
1StockReturnsStockTotal ReturnsPercentage of TotalRank
2W33W15437.75%1
3M44M12129.66%3
4L55L13332.60%2
5NA999
6W88
7M33
8L1
9NA999
10W33
11M44
12L77

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
F2=SUMIFS($B$2:$B$100,$A$2:$A$100,E2)
G2=F2/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")
H2=RANK.EQ(F2,$F$2:$F$4,0)
F3=SUMIFS($B$2:$B$100,$A$2:$A$100,E3)
G3=F3/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")
H3=RANK.EQ(F3,$F$2:$F$4,0)
F4=SUMIFS($B$2:$B$100,$A$2:$A$100,E4)
G4=F4/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")
H4=RANK.EQ(F4,$F$2:$F$4,0)

<tbody>
</tbody>

<tbody>
</tbody>
Thank you for your help Toadstool. Perhaps, there is some misunderstanding. What I want to do is to get the percent rank (=percentrank.inc(ray,cell)) of each stock under W, M, or L group. For example, under W, we have stocks 1,2,3,4, and I want to know the rank (percent) of each stock (1,2,3,4) within this W group. And same thing happens in M, and L groups. Do you have any idea to deal with this?
 
Upvote 0
Hi Rookieeee,

I'll assume the "NA" really is just text and not the result of a failed lookup "#N/A".

I wasn't sure what you meant by "percent rank" so I'm showing percent in one column and rank in another; you can delete the redundant column.

ABCDEFGH
1StockReturnsStockTotal ReturnsPercentage of TotalRank
2W33W15437.75%1
3M44M12129.66%3
4L55L13332.60%2
5NA999
6W88
7M33
8L1
9NA999
10W33
11M44
12L77

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
F2=SUMIFS($B$2:$B$100,$A$2:$A$100,E2)
G2=F2/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")
H2=RANK.EQ(F2,$F$2:$F$4,0)
F3=SUMIFS($B$2:$B$100,$A$2:$A$100,E3)
G3=F3/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")
H3=RANK.EQ(F3,$F$2:$F$4,0)
F4=SUMIFS($B$2:$B$100,$A$2:$A$100,E4)
G4=F4/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")
H4=RANK.EQ(F4,$F$2:$F$4,0)

<tbody>
</tbody>

<tbody>
</tbody>
Additionally, I can't form tables like what you do, because I also need to repeat this process over a long time period. Therefore, I think I need a repeatable formula so that I can simply drag and repeat.
 
Upvote 0
I'm not familiar with PERCENTRANK.INC and can't see how it would work with non-contiguous cells. The only solution I can offer is to build dynamic columns for the W, M and L values then do PERCENTRANK.INC of those.
 
Last edited:
Upvote 0
I'm not familiar with PERCENTRANK.INC and can't see how it would work with non-contiguous cells. The only solution I can offer is to build dynamic columns for the W, M and L values then do PERCENTRANK.INC of those.

Anyway, thank you very much
 
Upvote 0

Forum statistics

Threads
1,212,947
Messages
6,110,837
Members
448,302
Latest member
sniffit1st

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