SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-882795%22%20slang%3D%22en-US%22%3EHow%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882795%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20Rank%20a%20column%20using%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D((RANK(AS4%2C%24AS%243%3A%24AS900)%2Fnumber_of_stocks)*100)%2C%20however%20instead%20of%20the%20%24AS900%20component%2C%20I%20would%20like%20to%20vary%20the%20number%20of%20cells%20being%20ranked%20to%20a%20number%20of%20cells%20being%20determined%20by%20cell%20E4%20(which%20changes%2C%20as%20I%20change%20another%20variable)%E2%80%A6.would%20anyboby%20be%20able%20to%20help%20me%20with%20this%3F%26nbsp%3B%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-882795%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%202019%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-882965%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882965%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F362240%22%20target%3D%22_blank%22%3E%40rick01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20offset()%20function%20to%20manage%20cell%20ranges.%20I%20have%20used%20below%20formula%20in%20attached%20sample%20file.%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(RANK(B3%2COFFSET(%24B%243%3A%24B%24196%2C0%2C0%2C%24E%244%2C1))%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInput%20required%20row%20number%20in%20E4%20and%20let%20men%20know%20if%20is%20works%20as%20desired.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-882979%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882979%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F362240%22%20target%3D%22_blank%22%3E%40rick01%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdditionally%2C%20you%20can%20use%20the%20INDIRECT()%3A%3C%2FP%3E%3CP%3E%3D((RANK(AS4%2CINDIRECT(%22%24AS%243%3A%24AS%22%26amp%3B%24E%244)%2Fnumber_of_stocks)*100)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20if%20E4%3D800%2C%20then%20the%20function%20reads%3A%3C%2FP%3E%3CP%3E%3D((RANK(AS4%2C%24AS%243%3A%24AS800)%2Fnumber_of_stocks)*100)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-885507%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885507%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%2C%20thanks%20for%20getting%20back%20to%20me!%26nbsp%3B%20I%20tried%20the%20formula%2C%20but%20am%20getting%20a%20%22There's%20a%20problem%20with%20this%20formula%20message%22....any%20further%20advice%20you%20could%20give%20me%20would%20be%20much%20appreciated.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-885735%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885735%22%20slang%3D%22en-US%22%3EMy%20apologies.%20Would%20you%20be%20able%20to%20share%20a%20sample%20file%20so%20that%20I%20may%20test%20the%20formula%3F%20Please%20remember%20to%20keep%20any%20sensitive%20or%20confidential%20information%20out%20of%20the%20sample%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-885804%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885804%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F362240%22%20target%3D%22_blank%22%3E%40rick01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20appears%20that%20I%20missed%20a%20parenthesis.%20Try%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D((RANK(AS4%2CINDIRECT(%22%24AS%243%3A%24AS%22%26amp%3B%24E%244))%2Fnumber_of_stocks)*100)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888238%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888238%22%20slang%3D%22en-US%22%3E%3CP%3EGreat%20-%20works%20perfectly!%20You%20saved%20me%20a%20lot%20of%20time%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888261%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888261%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888401%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888401%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20previous%20help....I%20am%20just%20trying%20to%20apply%20the%20same%20logic%20to%20a%20PERCENTRANK.EXC%2C%20but%20am%20having%20trouble%20getting%20it%20to%20work%20-%20could%20you%20possibly%20help%20me%20on%20that%20as%20well%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I%20am%20trying%20to%20adapt%20is%3A%3C%2FP%3E%3CP%3E%3D((PERCENTRANK.EXC(%24I%243%3A%24I%24104%2CI4)))*10%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eagain%20I%20would%20like%20to%20replace%20I104%20with%20E4%20(the%20cell%20that%20contains%20the%20column%20length)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%3A%3C%2FP%3E%3CP%3E%3DIFNA(PERCENTRANK.EXC(I4%2COFFSET(%24I%243%3A%24I%241500%2C0%2C0%2C%24E%241%2C1))%2C%E2%80%9D%E2%80%9D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVery%20much%20appreciated%2C%20if%20you%20can%20help.%26nbsp%3B%20Thx%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888452%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888452%22%20slang%3D%22en-US%22%3E%3CP%3EHI%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F362240%22%20target%3D%22_blank%22%3E%40rick01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20below%20formula%2C%20hope%20it%20will%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000080%22%3E%3CSTRONG%3E%3DIFNA(((PERCENTRANK.EXC(OFFSET(%24I%243%3A%24I%24106%2C0%2C0%2C%24E%241%2C1)%2CI4)))*10%2C%22%22)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888482%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20RANK%2C%20but%20changing%20the%20number%20of%20components%20being%20ranked%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888482%22%20slang%3D%22en-US%22%3E%3CP%3EExcellent....thank%20you%20so%20much!%20%26nbsp%3B%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
rick01
Occasional 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
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

Highlighted

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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies