Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default '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!

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    246
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default 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)
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 'Percentrank' function under double sorts with 'NA's

    Quote Originally Posted by Toadstool View Post
    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?

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 'Percentrank' function under double sorts with 'NA's

    Quote Originally Posted by Toadstool View Post
    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.

  5. #5
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    246
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default 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.
    Last edited by Toadstool; Sep 4th, 2019 at 02:02 PM.
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    20
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 'Percentrank' function under double sorts with 'NA's

    Quote Originally Posted by Toadstool View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •