Forum Discussion

rick01's avatar
rick01
Copper Contributor
Sep 30, 2019
Solved

How to RANK, but changing the number of components being ranked automatically

I am trying to Rank a column using the following formula:

 

=((RANK(AS4,$AS$3:$AS900)/number_of_stocks)*100), however instead of the $AS900 component, I would like to vary the number of cells being ranked to a number of cells being determined by cell E4 (which changes, as I change another variable)….would anyboby be able to help me with this?  Thanks

Excel 2019
Formulas & Functions
  • tauqeeracma's avatar
    tauqeeracma
    Sep 30, 2019

    hi rick01 

    You can use offset() function to manage cell ranges. I have used below formula in attached sample file. 

     

    =IFERROR(RANK(B3,OFFSET($B$3:$B$196,0,0,$E$4,1)),"")

     

    Input required row number in E4 and let men know if is works as desired.

    Thanks

    Tauqeer

10 Replies

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Sep 30, 2019

    Hello rick01,

     

    Additionally, you can use the INDIRECT():

    =((RANK(AS4,INDIRECT("$AS$3:$AS"&$E$4)/number_of_stocks)*100)

     

    For example if E4=800, then the function reads:

    =((RANK(AS4,$AS$3:$AS800)/number_of_stocks)*100)

    • rick01's avatar
      rick01
      Copper Contributor
      Oct 01, 2019

      Hi PReagan, thanks for getting back to me!  I tried the formula, but am getting a "There's a problem with this formula message"....any further advice you could give me would be much appreciated. Thanks

      • PReagan's avatar
        PReagan
        Bronze Contributor
        Oct 01, 2019

        rick01 

        It appears that I missed a parenthesis. Try this:

        =((RANK(AS4,INDIRECT("$AS$3:$AS"&$E$4))/number_of_stocks)*100)

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Sep 30, 2019

    hi rick01 

    You can use offset() function to manage cell ranges. I have used below formula in attached sample file. 

     

    =IFERROR(RANK(B3,OFFSET($B$3:$B$196,0,0,$E$4,1)),"")

     

    Input required row number in E4 and let men know if is works as desired.

    Thanks

    Tauqeer

    • rick01's avatar
      rick01
      Copper Contributor
      Oct 02, 2019

      Hi tauqeeracma 

      Thanks for your previous help....I am just trying to apply the same logic to a PERCENTRANK.EXC, but am having trouble getting it to work - could you possibly help me on that as well?

       

      The formula I am trying to adapt is:

      =((PERCENTRANK.EXC($I$3:$I$104,I4)))*10

       

      again I would like to replace I104 with E4 (the cell that contains the column length)

       

      I have tried:

      =IFNA(PERCENTRANK.EXC(I4,OFFSET($I$3:$I$1500,0,0,$E$1,1)),””)

       

      Very much appreciated, if you can help.  Thx

      • tauqeeracma's avatar
        tauqeeracma
        Iron Contributor
        Oct 02, 2019

        HI rick01 

         

        Please try below formula, hope it will work.

         

        =IFNA(((PERCENTRANK.EXC(OFFSET($I$3:$I$106,0,0,$E$1,1),I4)))*10,"")

         

        Thanks,

        Tauqeer

         

    • rick01's avatar
      rick01
      Copper Contributor
      Oct 02, 2019

      Great - works perfectly! You saved me a lot of time !

Resources