Rank If?

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
 
B

Bernie Deitrick

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
 
F

FT

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

Bernie Deitrick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

rearranging formula suggestion 2
data splitting 1
List if 3
sorting array formula 2
data splitting 4
SQL query 1
How create ranking within subgroup 4
Macro to send email 5 days before a date mentioned in Cell 1

Top