Rank If?
 

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

Rank If?
 

FT,

In C2, enter the formula

=IF(1+SUMPRODUCT(($A$2:$A$XXX=A2)*($B$2:$B$XXXB2) )<=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


"FT" wrote in message
...
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

Rank If?
 

Thanks Bernie, worked like magic...had found a post you had replied to
with similar question after a google search.

Rank If?
 

Thanks Bernie, worked like magic...had found a post you had replied to
with similar question after a google search.

Me, too! ;-)

Gotta love Google...

Bernie
MS Excel MVP