Rank If?

  • Thread starter Thread starter FT
  • Start date Start date
F

FT

Here is my data


ColA-ColB-ColC
ABC-1-
ABC-4-
ABC-2-
DEF-4-
DEF-5-
DEF-10-
FGH-19-
FGH-12-
FGH-1-
FGH-4-
FGH-8-


Is there any formula I can use in Column C where it will input a 1 if
the number in Column B is ranked in the top two of Column A, and a 0
if it's not?

So final result would look like:

ColA-ColB-ColC
ABC-1-0
ABC-4-1
ABC-2-1
DEF-4-0
DEF-5-1
DEF-10-1
FGH-19-1
FGH-12-1
FGH-1-0
FGH-4-0
FGH-8-0
FGH-9
 
FT,

In C2, enter the formula

=IF(1+SUMPRODUCT(($A$2:$A$XXX=A2)*($B$2:$B$XXX>B2))<=2,1,0)

replace the XXX with the row number of your bottom values, then copy the formula down to match your
data set.

HTH,
Bernie
MS Excel MVP
 
Thanks Bernie, worked like magic...had found a post you had replied to
with similar question after a google search.
 
Thanks Bernie, worked like magic...had found a post you had replied to
with similar question after a google search.
Click to expand...

Me, too! ;-)

Gotta love Google...

Bernie
MS Excel MVP
 
Back
Top