Sep 30 2019 08:36 AM
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
Sep 30 2019 09:50 AM
Solutionhi @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
Sep 30 2019 09:58 AM - edited Sep 30 2019 10:02 AM
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)
Oct 01 2019 04:11 AM
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
Oct 01 2019 05:46 AM
Oct 01 2019 06:13 AM
It appears that I missed a parenthesis. Try this:
=((RANK(AS4,INDIRECT("$AS$3:$AS"&$E$4))/number_of_stocks)*100)
Oct 02 2019 01:00 AM
Great - works perfectly! You saved me a lot of time !
Oct 02 2019 01:08 AM
Oct 02 2019 02:18 AM
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
Oct 02 2019 02:51 AM
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
Oct 02 2019 03:11 AM - last edited on Nov 13 2023 10:10 PM by
Excellent....thank you so much! @tauqeeracma
Sep 30 2019 09:50 AM
Solutionhi @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