Results 1 to 6 of 6

Thread: Trying to find worst and best measures from a list containing duplicates

  1. #1
    New Member
    Join Date
    Feb 2010
    Location
    UK
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Trying to find worst and best measures from a list containing duplicates

    I am trying to do something that should be simple, by my brain is having a dumb block.

    I have a table of sites showing their ranking for various performance measures.
    I just want to create a list showing which is their best and worst measures, but I'm getting confused where they may be the same rank across various measures.

    Would anyone please be kind enough to point me in the right direction...

    Site 1 Site 2 Site 3 Site 4
    Measure 1 2 3 1 4
    Measure 2 1 2 3 3
    Measure 3 4 1 4 1
    Measure 4 3 2 1 4
    Measure 5 2 1 3 4
    Site 1 Site 2 Site 3 Site 4
    Best Measure Measure 2
    2nd Best Measure Measure 1
    3rd Best Measure Measure 5
    Site 1 Site 2 Site 3 Site 4
    Worst Measure
    2nd Wort Measure
    3rd Worst Measure

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Trying to find worst and best measures from a list containing duplicates

    The formulas go

     ABCDE
    1 Site 1Site 2Site 3Site 4
    2Measure 12314
    3Measure 21233
    4Measure 34141
    5Measure 43214
    6Measure 52134
    7     
    8 Site 1Site 2Site 3Site 4
    9Best Measure1   
    102nd Best Measure2   
    113rd Best Measure2   
    12     
    13 Site 1Site 2Site 3Site 4
    14Best Measure4   
    152nd Best Measure3   
    163rd Best Measure2   

    Formeln der Tabelle
    ZelleFormel
    B9=SMALL(B$2:B$6,1)
    B10=SMALL(B$2:B$6,2)
    B11=SMALL(B$2:B$6,3)
    B14=LARGE(B$2:B$6,1)
    B15=LARGE(B$2:B$6,2)
    B16=LARGE(B$2:B$6,3)
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Feb 2010
    Location
    UK
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to find worst and best measures from a list containing duplicates

    Thanks, but that’s not quite what I need. I want it to show the “name” of the best measures and worst measures.

    my problem is that one site may he say, 2nd best for three different measures, then say 4th for another measure, so the sheet needs to look up the names of the best performing measures (and also worst, which I’m sure I can work out once I get my head round it).

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Trying to find worst and best measures from a list containing duplicates

    Sorry. You're right, you asked for the name.Let's try an auxiliary column for each Site. The good news is that it is the same formula for all auxiliary columns. The same for the best and worst, is the same formula.


     ABCDEFGHIJ
    1 Site 1Site 2Site 3Site 4 Site 1Site 2Site 3Site 4
    2Measure 12314 3513
    3Measure 21233 1332
    4Measure 31141 2151
    5Measure 43214 5424
    6Measure 52134 4245
    7          
    8 Site 1Site 2Site 3Site 4     
    9Best MeasureMeasure 2Measure 3Measure 1Measure 3     
    102nd Best MeasureMeasure 3Measure 5Measure 4Measure 2     
    113rd Best MeasureMeasure 1Measure 2Measure 2Measure 1     
    12          
    13 Site 1Site 2Site 3Site 4     
    14Worst MeasureMeasure 4Measure 1Measure 3Measure 5     
    152nd Worst MeasureMeasure 5Measure 4Measure 5Measure 4     
    163rd Worst MeasureMeasure 1Measure 2Measure 2Measure 1     

    CellFormula
    G2=COUNTIF(B$2:B$6,"<"&B2)+1+COUNTIF(B$1:B1,B2)
    B9=INDEX($A$2:$A$6,MATCH(SMALL(G$2:G$6,ROW()-(ROW($B$8))),G$2:G$6,0))
    B14=INDEX($A$2:$A$6,MATCH(LARGE(G$2:G$6,ROW()-ROW($B$13)),G$2:G$6,0))
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    Feb 2010
    Location
    UK
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to find worst and best measures from a list containing duplicates

    Brilliant, I think that does it for me. THANK YOU!

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,239
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Trying to find worst and best measures from a list containing duplicates

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •