# Thread: 'Percentrank' function under double sorts with 'NA's Thanks: 0 Likes: 0

1. ## 'Percentrank' function under double sorts with 'NA's

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!  Reply With Quote

2. ## Re: 'Percentrank' function under double sorts with 'NA's

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.

A B C D E F G H
1 Stock Returns Stock Total Returns Percentage of Total Rank
2 W 33 W 154 37.75% 1
3 M 44 M 121 29.66% 3
4 L 55 L 133 32.60% 2
5 NA 999
6 W 88
7 M 33
8 L 1
9 NA 999
10 W 33
11 M 44
12 L 77
Sheet5

Worksheet Formulas
Cell Formula
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)  Reply With Quote

3. ## Re: 'Percentrank' function under double sorts with 'NA's Originally Posted by Toadstool 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.

A B C D E F G H
1 Stock Returns Stock Total Returns Percentage of Total Rank
2 W 33 W 154 37.75% 1
3 M 44 M 121 29.66% 3
4 L 55 L 133 32.60% 2
5 NA 999
6 W 88
7 M 33
8 L 1
9 NA 999
10 W 33
11 M 44
12 L 77
Sheet5

Worksheet Formulas
Cell Formula
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)
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?  Reply With Quote

4. ## Re: 'Percentrank' function under double sorts with 'NA's Originally Posted by Toadstool 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.

A B C D E F G H
1 Stock Returns Stock Total Returns Percentage of Total Rank
2 W 33 W 154 37.75% 1
3 M 44 M 121 29.66% 3
4 L 55 L 133 32.60% 2
5 NA 999
6 W 88
7 M 33
8 L 1
9 NA 999
10 W 33
11 M 44
12 L 77
Sheet5

Worksheet Formulas
Cell Formula
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)
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.  Reply With Quote

5. ## Re: 'Percentrank' function under double sorts with 'NA's

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.  Reply With Quote

6. ## Re: 'Percentrank' function under double sorts with 'NA's Originally Posted by Toadstool 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  Reply With Quote

## User Tag List

calculate, group, guys, nas, stock 