Using Rank with If

S

SteveC

Column B contains numbers.

Column C should rank numbers in Column B, but only for sets of data matched
by the Xs (X, XX, XXX, XXXX, etc.)

In other words, rank all figures in Column B for X, then rank all figures in
Column B for XXm, etc...

Thanks for your help!

Column A Column B Column C
X 2
X
X
X
X
X
XX
XX
XX
XX
XXX
XXX
XXX
XXX
XXX
XXX
XXX
XXXX
XXXX
XXXX
XXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXX
XXXXXX
XXXXXX
XXXXXX
XXXXXX
XXXXXX
XXXXXX
XXXXXXX
 
D

Domenic

Assuming that A2:B10 contains the data, to rank Column B from highest to
lowest, try...

C2, copied down:

=SUMPRODUCT(--(A$2:A$10=A2),--(B2<B$2:B$10))+1

Hope this helps!
 
S

SteveC

Hey thanks! I get funky numbers using that. let me clarify:

a formula that in Column C that returns the same values I'm putting in below.

I'm currently using the Rank Function, but I only know how to change the
rank references manually for each data label. Am looking for a formula I can
drag down 2000+ rows and rank the data in Column B.

Col A Col B Col C
Rank by
Label Numbers Label

X 4.0 3
X 2.8 6
X 7.3 2
X 11.7 1
X 3.0 4
X 2.8 5
XX 4.8 3
XX 1.3 4
XX 15.7 2
XX 15.7 1
XXX 10.6 2
XXX 12.1 1
XXX 1.5 4
XXX 0.9 6
XXX 1.3 5
XXX 0.5 7
XXX 1.7 3
XXXX 1.0 4
XXXX 5.6 1
XXXX 5.4 2
XXXX 1.8 3
XXXXX 6.4 1
XXXXX 3.7 3
XXXXX 5.5 2
XXXXX 2.8 4
XXXXX 1.0 5
XXXXXX 3.4 3
XXXXXX 3.8 1
XXXXXX 2.1 4
XXXXXX 1.7 6
XXXXXX 2.0 5
XXXXXX 3.8 2
XXXXXXX 3.5 1
 

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

Count 3
arrange data 2
Multilevel List 3
Auto Merge text from multiple sources 0
Help cleaning up bad paragraph breaks 2
Find duplicate 0
How...? 0
Time punch data from rows to columns 3

Top