Rank with if command

G

gennario

Here is my situation. I have a group of numbers in column a that I need
ranked. The catch is that I only want to rank the numbers that have the word
"qualified" in column b. I tried using the "if" command but it ranks all the
numbers and shows 0 for those without the qualified but still ranks the 10th
number as 10. I want to have the 5 numbers that are qualified ranked 1 to
5...Is this possible???
 
B

Bryan Hessey

Hi,

I think the attached might answer your question, but with the use of a
additional helper column.



Here is my situation. I have a group of numbers in column a that I need
ranked. The catch is that I only want to rank the numbers that have th
word
"qualified" in column b. I tried using the "if" command but it rank
all the
numbers and shows 0 for those without the qualified but still ranks th
10th
number as 10. I want to have the 5 numbers that are qualified ranked
to
5...Is this possible??
Click to expand...

+-------------------------------------------------------------------
|Filename: Rank.zip
|Download: http://www.excelforum.com/attachment.php?postid=3841
+-------------------------------------------------------------------
 
K

kk

Hi,

Try this array formula in C2...


=IF($B2<>"qualified","",MATCH($A2,LARGE(IF($B$2:$B$11="qualified",$A$2:$A$11),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$11,"qualified")))),0))

Confirmed the formula by Ctrl + Shift + Enter



Here is my situation. I have a group of numbers in column a that I need
ranked. The catch is that I only want to rank the numbers that have the word
"qualified" in column b. I tried using the "if" command but it ranks all the
numbers and shows 0 for those without the qualified but still ranks the 10th
number as 10. I want to have the 5 numbers that are qualified ranked 1 to
5...Is this possible???
 
G

gennario

I have no idea what you did but it works like a charm!!!!! I will probably be
spending the next 2 weeks disecting this formula to see how it
works----THANKS - Let me know next time you run an excel class, I'll sign
up!!!
 
D

Domenic

Here's another way...

C2, copied down:

=IF(B2="Qualified",SUMPRODUCT(--($B$2:$B$11="Qualified"),--(A2<$A$2:$A$11
))+1,"")

Hope this helps!
 
Top