SOLVED

Ranking Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2524561%22%20slang%3D%22en-US%22%3ERanking%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2524561%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20have%20Column%20P%20rank%20the%20players%20relative%20to%20their%20position.%20To%20clarify%2C%20Devante%20Adams%20in%20A7%20has%20an%20Average%20Rank%20of%206%20(found%20in%20O7)%2C%20yet%20is%20the%20%231%20WR%20(position%20shown%20in%20Column%20B).%20So%2C%20the%20value%20in%20P7%20should%20be%201.%20Then%2C%20if%20Stephon%20Diggs%20in%20A9%20has%20an%20average%20rank%20of%208%20(found%20in%20O9)%2C%20the%20value%20in%20P9%20should%20be%202.%20This%20is%20because%20he%20has%20the%20second%20highest%20average%20rank%20of%20all%20players%20with%20%22WR%22%20in%20Column%20B.%20How%20can%20I%20do%20this%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mcwinters19_0-1625684865178.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294120i9007FA239C6F02F9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22mcwinters19_0-1625684865178.png%22%20alt%3D%22mcwinters19_0-1625684865178.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2524561%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2525389%22%20slang%3D%22en-US%22%3ERe%3A%20Ranking%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2525389%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1097935%22%20target%3D%22_blank%22%3E%40mcwinters19%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20little%20tricky.%20I%20would%20add%20some%20helper%20columns%20for%20each%20position%20to%20help%20track.%20You'll%20have%20to%20change%20the%20columns%20around%20but%20something%20like%20this%20should%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20helper%20columns%3A%3C%2FP%3E%3CP%3E%3DIF(%24O195%3DP%24193%2C1%2BCOUNT(P%24194%3AP194)%2C%22%22)%3C%2FP%3E%3CP%3EIt'll%20count%20each%20occurrence%20and%20rank%20them%20in%20order.%20From%20there%2C%20you'll%20need%20to%20do%20another%20xlookup%20for%20each%20player%20like%20in%20your%20previous%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20can%20work%20this%20to%20fit%20your%20workbook%20or%20if%20you%20need%20more%20explanation!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DKoontz_2-1625692228775.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294146iBA385EFA7FEF0F19%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22DKoontz_2-1625692228775.png%22%20alt%3D%22DKoontz_2-1625692228775.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DKoontz_3-1625692239482.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294147i2478DDC103F45340%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22DKoontz_3-1625692239482.png%22%20alt%3D%22DKoontz_3-1625692239482.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I would like to have Column P rank the players relative to their position. To clarify, Devante Adams in A7 has an Average Rank of 6 (found in O7), yet is the #1 WR (position shown in Column B). So, the value in P7 should be 1. Then, if Stephon Diggs in A9 has an average rank of 8 (found in O9), the value in P9 should be 2. This is because he has the second highest average rank of all players with "WR" in Column B. How can I do this?

mcwinters19_0-1625684865178.png

 

1 Reply
best response confirmed by mcwinters19 (New Contributor)
Solution

@mcwinters19 

 

This is a little tricky. I would add some helper columns for each position to help track. You'll have to change the columns around but something like this should work.

 

In the helper columns:

=IF($O195=P$193,1+COUNT(P$194:P194),"")

It'll count each occurrence and rank them in order. From there, you'll need to do another xlookup for each player like in your previous question.

 

Let me know if you can work this to fit your workbook or if you need more explanation!

 

 

DKoontz_2-1625692228775.pngDKoontz_3-1625692239482.png