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

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)

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Oct 01, 2019
    My apologies. Would you be able to share a sample file so that I may test the formula? Please remember to keep any sensitive or confidential information out of the sample file.

Resources