Return text string for highest duplicate values

Question

I've been battling with a google sheets conundrum so hoping someone can help.

In a nutshell, I'm trying to achieve the following based on the image below:

• Where the name is the same in column C
• Return 'winner', 'tied' or 'loser' in column F based on the respective value in column D.
  • Highest single value in D = 'winner'
  • Two or more identical highest values in D = 'tied'
  • Any value that is not the highest in D = 'loser'

I currently have it working with a helper column that uses a combination of ArrayFormula, IF and the MAX functions that returns the text 'winner' and 'loser' in column F.

But this falls over when I try to introduce a 'tied' result to column G.

I've tried using Google Sheet's QUERY function, but to no avail.

Here's an open sheet to have a play around with if anyone has any ideas: https://docs.google.com/spreadsheets/d/1FDEja_QIGUIWcFpbZ8-FtPquBCUCkq3TLIHG2TT1OMI/edit?usp=sharing

^{(Click image to enlarge)}

Answer 1

Using Helper data the issue can be fixed.

:Caveat:

• Below shown method works with Google Sheet as well Excel.
• This method considers MAXIMUM value for Kilman is 2.8 and for Kelchi is 3.9.

How it works:

• Formula in cell H2 finds RANK:

  =IF(ISBLANK(D2),"",SUMPRODUCT(--(F2=$F$2:$F$7),(--(G2<$G$2:$G$7)))+1)
  

• Final formula in cell I2:

   =IFERROR(IF(AND($F$2:$F$7=F2,H2=1),"Tied",IF(AND($F$2:$F$7=F2,H2>3),"Looser","Winner")),"")
  

N.B.

• For Kilman G2 & G4 are TIED, since his Max value is 2.8 .
• G3 is WINNER since is less than the Max (2.8) and higher than the lowest (2.3).
• And, for Kelchi WINNER is G6 since is less than his max 3.9.
• H2>3 in the formula in cell I2 is Eitable/Adjustable since the lowest RANK is 4.

Adjust cell references in the formula as needed.

Answer 2

As Scott Cranersays, which version? Which program even? Well, I'll forge forth...

Use something like the following. The approach is valid and can be adapted to version and/or program. And while I will use today's Excel, each thing done has older ways of doing it that you can adapt each bit for.

Use: (no need for a helper column, just insert where the final answer is desired)

=LET(  MaxBid,  MAX(FILTER($D$2:$D$7,$C$2:$C$7=C2)),    NameBid,  FILTER($D$2:$D$7,$C$2:$C$7=C2),
       IF(D2<>MaxBid,  "Loser",  IF(COUNTA(NameBid)>1,  "Tied",  "Winner"  )  ))

(Obviously, adjust cell references to fit the real ranges.)

The basic approach is to first isolate on each name in the list, so you have an output for every line, but to use MAX() to find the highest bid value for each name, whatever it might be, and in the form of an internal array that the formula makes use of. Once you have the high bid for each name, each line's name is tested for win, tie, or lose vs. that MaxBid that the name has. If the bid for that line is not the MaxBid value, it is by definition a loser and the oouter IF() outputs that. If it DID match the highest bid, the inner IF() tests to see if more than one occurrence of the MaxBid exists. If so, it outputs "Tied" and if there are NOT more than one occurrences, it outputs "Winner" which covers all the possible ways for it to play out.

If you cannot use LET() (you don't have it, or perhaps it's users who may not have it, just replace the names it uses with the formula pieces it is given for their outputs. If you don't have FILTER(), you can easily replace it as there are several "old" ways of doing its work. If you don't ahve SPILL functionality, use {CSE} array entry instead. And if you only have Google, not Excel, it's probably even easier to substitute techniques.

If one knew what your program situation really were, there'd be a LOT mor eincentive to just provide a finished formula instead of "edit this and paste that" vagueness. Ah well.