Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Max Value Lookup (Excel 2002 (xp) SP2)

    Looking for a way to "Lookup" a value within categories based on a Min or Max value. For example: I am building a worksheet to keep track of race times. There are different categories of racers. I have a summary range that tracks fastest time within categories, etc. I want to be able to draw out the name (or names, if there is a tie) of the individuals who have the fastest times. This would involve some creatives use of the Lookup function. Evidently, I am not creative enough to figure it out on my own.

    For the simplicity of documenting times during the races, I am trying to keep all racers on one list, instead of dividing them by categories on different sheets. However, this makes for more complex formulas. If need be, I can divide them up, but thought I'd ask if anyone can do this as a single list.

    Any suggestions would be greatly appreciated.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Max Value Lookup (Excel 2002 (xp) SP2)

    Does the thread starting in <post#=309259>post 309259</post#> answer your questions enough?

    Steve

  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max Value Lookup (Excel 2002 (xp) SP2)

    Maybe an advanced filter that copies the wanted data to a new location. Then do your lookups based on that new location. It would be nice to have a button that would refresh the advanced filter if you wanted to sort on different criteria.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max Value Lookup (Excel 2002 (xp) SP2)

    Interesting discussion. I think I got some direction to go. Thanks

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Max Value Lookup (Excel 2002 (xp) SP2)

    If you need more specific info, don't hesitate to ask. The more specific the question, the more specific we can answer.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max Value Lookup (Excel 2002 (xp) SP2)

    A different approach to determine the top N...

    What follows takes up the race data by single category in order to keep required computations simple and efficient.

    Let A2:B16 house the following sample that also includes the headers/labels:

    {"Category A","Time";
    "Bill",1.252195;
    "Peter",1.604835;
    "Charles",1.604835;
    "John",2.192979;
    "Steve",1.452168;
    "Rich",2.54389;
    "Ted",2.168611;
    "Karl",1.962035;
    "Damon",1.252195;
    "Etienne",1.362845;
    "Karel",2.478195;
    "Chris",2.98939;
    "Antonio",1.252195;
    "Mete",2.202543}

    In C3 enter & copy down:

    =IF(ISNUMBER(B3),RANK(B3,$B$3:$B$16,1)+COUNTIF($B$ 3:B3,B3)-1,"")

    In F2 enter:

    =MAX(IF(INDEX(B3:B16,MATCH(G2,C3:C16,0))=B3:B16,C3 :C16))-G2

    which must be confirmed with control+shift+enter instead of just with enter.

    In G2 enter: 1 [ This is Top N parameter set by the user. ]

    In F3 enter: Racer [ which is just a label ]

    In G3 enter: Fastest Time [ which is just a label ]

    In F4 enter & copy down:

    =IF(G4<>"",INDEX($A$3:$A$16,MATCH(ROW()-ROW($F$4)+1,$C$3:$C$16,0)),"")

    In G4 enter & copy down:

    =IF(ROW()-ROW($G$4)+1<=$G$2+$F$2,INDEX($B$3:$B$16,MATCH(ROW( )-ROW($G$4)+1,$C$3:$C$16,0)),"")

    Note that the ROW($F$4) and ROW($G$4) bits refer to the cells where the respective formulas are first entered in order to guarantee robustness against inserting rows before the results area.

    This is how the results area would look like:

    {2,1;"Racer","Fastest Time";"Bill",1.252195;"Damon",1.252195;"Antonio",1 .252195;"","";"",""}

    As can be seen, the above scheme is intended to handle the ties regarding the Nth position dynamically.

    Aladin

    P.S. If needed, I can upload an Excel file showing the above scheme.
    Microsoft MVP - Excel

Posting Permissions

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