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

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

    Excellent....thank you so much!   tauqeeracma 

Resources