SOLVED

Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2348229%22%20slang%3D%22en-US%22%3EExcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2348229%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20Would%20appreciate%20help%20with%20the%20following%20matter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20establish%20a%20ranked%20table%20based%20on%20a%20certain%20condition.%20My%20table%20relates%20to%20a%20series%20of%20cover%20designs%20which%20have%20been%20rated%20to%20produce%20individual%20scores%20out%20of%205.%20However%2C%20I%20only%20want%20to%20rank%20those%20covers%20that%20have%20been%20rated%20on%205%20or%20more%20occasions%20(as%20indicated%20in%20column%202)%20such%20that%20Cover3%20would%20not%20be%20ranked%20at%20all%20and%20that%20Cover13%20would%20be%20ranked%20%231.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20tried%20various%20approaches%20with%20the%20RANK%20formula%20but%20to%20no%20avail.%20Would%20therefore%20be%20grateful%20for%20any%20suggestions%20that%20will%20help%20crack%20the%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22288%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2272%22%20height%3D%2220%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272%22%3ERatings%3C%2FTD%3E%3CTD%20width%3D%2272%22%3EScore%3C%2FTD%3E%3CTD%20width%3D%2272%22%3ERank%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover1%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E3.33%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover2%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E3.50%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover3%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E5.00%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover4%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E4.50%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover5%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E3.78%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover6%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E2.75%3C%2FTD%3E%3CTD%3E17%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover7%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E4.44%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover8%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3E3.73%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover9%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3.50%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover10%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E3.67%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover11%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E3.00%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover12%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E3.50%3C%2FTD%3E%3CTD%3E13%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover13%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E5.00%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover14%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E4.00%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover15%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E3.00%3C%2FTD%3E%3CTD%3E16%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover16%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E1.00%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover17%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E2.50%3C%2FTD%3E%3CTD%3E19%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover18%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E4.33%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover19%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E2.57%3C%2FTD%3E%3CTD%3E18%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2219%22%3ECover20%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E5.00%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2348229%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2348449%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2348449%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1024398%22%20target%3D%22_blank%22%3E%40Iriwel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20D2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(B2%26gt%3B%3D5%2CCOUNTIFS(%24B%242%3A%24B%2421%2C%22%26gt%3B%3D5%22%2C%24C%242%3A%24C%2421%2C%22%26gt%3B%22%26amp%3BC2)%2B1%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2348601%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2348601%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20reply%20Hans%3B%20it%20works%20a%20treat%20and%20I%20would%20certainly%20not%20have%20arrived%20at%20this%20conclusion%20under%20my%20own%20steam.%20However%2C%20one%20additional%20matter%3B%20I%20would%20like%20the%20ranking%20results%20to%20be%20presented%20as%20a%20set%20of%20unique%20numbers.%20Is%20there%20a%20solution%3F%3CBR%20%2F%3E%3CBR%20%2F%3ELook%20forward%20to%20your%20reply.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello. Would appreciate help with the following matter.

 

I am trying to establish a ranked table based on a certain condition. My table relates to a series of cover designs which have been rated to produce individual scores out of 5. However, I only want to rank those covers that have been rated on 5 or more occasions (as indicated in column 2) such that Cover3 would not be ranked at all and that Cover13 would be ranked #1.

 

Have tried various approaches with the RANK formula but to no avail. Would therefore be grateful for any suggestions that will help crack the problem.

 

 RatingsScoreRank
Cover133.3314
Cover2123.5011
Cover315.001
Cover424.504
Cover593.788
Cover642.7517
Cover794.445
Cover8153.739
Cover923.5012
Cover1093.6710
Cover1113.0015
Cover1253.5013
Cover1395.002
Cover14144.007
Cover1563.0016
Cover1671.0020
Cover1762.5019
Cover1834.336
Cover1982.5718
Cover2035.003

 

Thanks in advance.

4 Replies

@Iriwel 

In D2:

 

=IF(B2>=5,COUNTIFS($B$2:$B$21,">=5",$C$2:$C$21,">"&C2)+1,"")

 

Fill down.

Thanks for your reply Hans; it works a treat and I would certainly not have arrived at this conclusion under my own steam. However, one additional matter; I would like the ranking results to be presented as a set of unique numbers. Is there a solution?

Look forward to your reply.
best response confirmed by Iriwel (Occasional Contributor)
Solution

@Iriwel 

In D2:

 

=IF(B2>=5, COUNTIFS($B$2:$B$21, ">=5", $C$2:$C$21, ">"&C2) + COUNTIFS($B$2:$B2, ">=5", $C$2:$C2, C2), "")

 

Fill down.

Hans, again my very grateful thanks for your help.

Regards

James