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

R

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

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 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

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Stock[/TD]
[TD="align: center"]Returns[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Stock[/TD]
[TD="align: center"]Total Returns[/TD]
[TD="align: center"]Percentage of Total[/TD]
[TD="align: center"]Rank[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]154[/TD]
[TD="align: center"]37.75%[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]121[/TD]
[TD="align: center"]29.66%[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]133[/TD]
[TD="align: center"]32.60%[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]NA[/TD]
[TD="align: center"]999[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]NA[/TD]
[TD="align: center"]999[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUMIFS($B$2:$B$100,$A$2:$A$100,E2)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=F2/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=RANK.EQ(F2,$F$2:$F$4,0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=SUMIFS($B$2:$B$100,$A$2:$A$100,E3)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=F3/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H3[/TH]
[TD="align: left"]=RANK.EQ(F3,$F$2:$F$4,0)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=SUMIFS($B$2:$B$100,$A$2:$A$100,E4)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=F4/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H4[/TH]
[TD="align: left"]=RANK.EQ(F4,$F$2:$F$4,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Toadstool said:
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

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Stock[/TD]
[TD="align: center"]Returns[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Stock[/TD]
[TD="align: center"]Total Returns[/TD]
[TD="align: center"]Percentage of Total[/TD]
[TD="align: center"]Rank[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]154[/TD]
[TD="align: center"]37.75%[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]121[/TD]
[TD="align: center"]29.66%[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]133[/TD]
[TD="align: center"]32.60%[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]NA[/TD]
[TD="align: center"]999[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]NA[/TD]
[TD="align: center"]999[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=SUMIFS($B$2:$B$100,$A$2:$A$100,E2)[/TD]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=F2/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")[/TD]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=RANK.EQ(F2,$F$2:$F$4,0)[/TD]
[/TR]
[TR]
[TH]F3[/TH]
[TD="align: left"]=SUMIFS($B$2:$B$100,$A$2:$A$100,E3)[/TD]
[/TR]
[TR]
[TH]G3[/TH]
[TD="align: left"]=F3/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")[/TD]
[/TR]
[TR]
[TH]H3[/TH]
[TD="align: left"]=RANK.EQ(F3,$F$2:$F$4,0)[/TD]
[/TR]
[TR]
[TH]F4[/TH]
[TD="align: left"]=SUMIFS($B$2:$B$100,$A$2:$A$100,E4)[/TD]
[/TR]
[TR]
[TH]G4[/TH]
[TD="align: left"]=F4/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")[/TD]
[/TR]
[TR]
[TH]H4[/TH]
[TD="align: left"]=RANK.EQ(F4,$F$2:$F$4,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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
Toadstool said:
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

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Stock[/TD]
[TD="align: center"]Returns[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Stock[/TD]
[TD="align: center"]Total Returns[/TD]
[TD="align: center"]Percentage of Total[/TD]
[TD="align: center"]Rank[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]154[/TD]
[TD="align: center"]37.75%[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]121[/TD]
[TD="align: center"]29.66%[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]133[/TD]
[TD="align: center"]32.60%[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]NA[/TD]
[TD="align: center"]999[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]NA[/TD]
[TD="align: center"]999[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=SUMIFS($B$2:$B$100,$A$2:$A$100,E2)[/TD]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=F2/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")[/TD]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=RANK.EQ(F2,$F$2:$F$4,0)[/TD]
[/TR]
[TR]
[TH]F3[/TH]
[TD="align: left"]=SUMIFS($B$2:$B$100,$A$2:$A$100,E3)[/TD]
[/TR]
[TR]
[TH]G3[/TH]
[TD="align: left"]=F3/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")[/TD]
[/TR]
[TR]
[TH]H3[/TH]
[TD="align: left"]=RANK.EQ(F3,$F$2:$F$4,0)[/TD]
[/TR]
[TR]
[TH]F4[/TH]
[TD="align: left"]=SUMIFS($B$2:$B$100,$A$2:$A$100,E4)[/TD]
[/TR]
[TR]
[TH]G4[/TH]
[TD="align: left"]=F4/SUMIFS($B$2:$B$100,$A$2:$A$100,"<>NA")[/TD]
[/TR]
[TR]
[TH]H4[/TH]
[TD="align: left"]=RANK.EQ(F4,$F$2:$F$4,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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
Toadstool said:
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.
Click to expand...

Anyway, thank you very much
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Sorting: Prioritizing Group retention over Order
Replies
0
Views
75
stretch2011
S
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
2K
felixstraube
F
etaf
  • Solved
SORT - an Extract List based on criteria - BUT 2019 Version ONLY - NOT 365
Replies
9
Views
807
Georgiboy
Georgiboy
E
  • Question Question
FIFO Macro giving different results each time it runs
Replies
7
Views
1K
Lee Wei Sin
L
M
  • Question Question
multiple optionbuttons on userform to merge multiple sheets and calculate stock
Replies
0
Views
620
Mussa
M

Forum statistics

Threads
1,224,749
Messages
6,180,727
Members
452,995
Latest member
isldboy

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