Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington
    Posts
    251
    Thanks
    29
    Thanked 2 Times in 2 Posts

    DMAX ?? (Office XP)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I have fifteen teams; each team has a score. I want to display the team with the highest score. I can use Data Sort and get the list sorted. I don't want to do that. I want the name and score of the winning team in another area of the worksheet. I've tried DMAX and it gives me the amount, but I can't figure out how to get the name of the team.
    Bob Wall

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: DMAX ?? (Office XP)

    It depends a bit on how your data is laid out, and also, we'd need to know how you are using =DMAX. If the data was in columns A & B, like this, I would add a column C duplicating team names and use VLOOKUP:
    <pre> a b c
    1 red 3 =A2
    2 blue 2 =A3
    3 orange 3 =A4
    4 green 4 =A5
    5 black 1 =A6
    6 white 2 =A7
    7 yellow 2 =A8</pre>

    high scoring team would be =VLOOKUP(MAX(B1:B7),$B$1:$C$7,2,FALSE). You'll need a more complex approach if you have co-winners.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington
    Posts
    251
    Thanks
    29
    Thanked 2 Times in 2 Posts

    Re: DMAX ?? (Office XP)

    I tried your method. The MAX part works, and returns the highest score. The second part, that which should return the team name, almost always returns one of the last two names in the team list, in column A; even though that team does not have the highest score. I tried it with RANDBETWEEN and values, with the same results.

    I'm enclosing a copy of the worksheet.
    Bob Wall

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DMAX ?? (Office XP)

    Sorry about that. I left off the third parameter to the Match function, which I then copied and pasted into another answer making it wrong also. Try the corrected formyla below:

    <pre>=MAX(B1:B15)
    =OFFSET(A1,MATCH(MAX(B1:B15),B1:B15,0)-1,0)
    </pre>

    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DMAX ?? (Office XP)

    If the teams are in A1:A15, and their scores are in B1:B15, then the following two formulas should give you the high score and the team name:

    <pre>=MAX(B1:B15)
    =OFFSET(A1,MATCH(MAX(B1:B15),B1:B15)-1,0)
    </pre>


    This assumes that there will not be two teams with the same high score.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington
    Posts
    251
    Thanks
    29
    Thanked 2 Times in 2 Posts

    Re: DMAX ?? (Office XP)

    Thank you for your assistance. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    Bob Wall

Posting Permissions

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