Using Rank with If

[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

 

[Biff]

Hi!

Try this in column C:

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

Biff

 

[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!

 

[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

 

[SteveC]

Bravo! Works great. Thanks very much.

 

[SteveC]

Sorry, my mistake. Your formula works perfectly.

Bravissimo! Thanks very much!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff