SOLVED

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

Copper Contributor

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

10 Replies
best response confirmed by rick01 (Copper Contributor)
Solution

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

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)

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

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.

@rick01 

It appears that I missed a parenthesis. Try this:

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

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

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

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

 

Excellent....thank you so much!   @tauqeeracma 

1 best response

Accepted Solutions
best response confirmed by rick01 (Copper Contributor)
Solution

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

View solution in original post