 • 410K Members
• 7,905 Online
• 466K Conversations
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

10 Replies
Solution

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

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

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

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)

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

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

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

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.

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

It appears that I missed a parenthesis. Try this:

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

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

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

You’re welcome

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

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

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

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

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

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies