Using Rank with If
 

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

Using Rank with If
 

Hi!

Try this in column C:

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

Biff

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

Using Rank with If
 

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!

In article ,
SteveC wrote:

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

Using Rank with If
 

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

Using Rank with If
 

Bravo! Works great. Thanks very much.

Using Rank with If
 

Sorry, my mistake. Your formula works perfectly.

Bravissimo! Thanks very much!

Using Rank with If
 

You're welcome. Thanks for the feedback!

Biff

"SteveC" wrote in message
...
Sorry, my mistake. Your formula works perfectly.

Bravissimo! Thanks very much!