software

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

Try this in column C:

=SUMPRODUCT(--($A$2:$A$34=A2),--(B2lt;$B$2:$B$34)) 1

Biff

quot;SteveCquot; gt; wrote in message
...
gt; Column B contains numbers.
gt;
gt; Column C should rank numbers in Column B, but only for sets of data
gt; matched
gt; by the Xs (X, XX, XXX, XXXX, etc.)
gt;
gt; In other words, rank all figures in Column B for X, then rank all figures
gt; in
gt; Column B for XXm, etc...
gt;
gt; Thanks for your help!
gt;
gt; Column A Column B Column C
gt; X 2
gt; X
gt; X
gt; X
gt; X
gt; X
gt; XX
gt; XX
gt; XX
gt; XX
gt; XXX
gt; XXX
gt; XXX
gt; XXX
gt; XXX
gt; XXX
gt; XXX
gt; XXXX
gt; XXXX
gt; XXXX
gt; XXXX
gt; XXXXX
gt; XXXXX
gt; XXXXX
gt; XXXXX
gt; XXXXX
gt; XXXXXX
gt; XXXXXX
gt; XXXXXX
gt; XXXXXX
gt; XXXXXX
gt; XXXXXX
gt; XXXXXXX
gt;
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),--(B2lt;B$2:B$10)) 1

Hope this helps!

In article gt;,
SteveC gt; wrote:

gt; Column B contains numbers.
gt;
gt; Column C should rank numbers in Column B, but only for sets of data matched
gt; by the Xs (X, XX, XXX, XXXX, etc.)
gt;
gt; In other words, rank all figures in Column B for X, then rank all figures in
gt; Column B for XXm, etc...
gt;
gt; Thanks for your help!
gt;
gt; Column A Column B Column C
gt; X 2
gt; X
gt; X
gt; X
gt; X
gt; X
gt; XX
gt; XX
gt; XX
gt; XX
gt; XXX
gt; XXX
gt; XXX
gt; XXX
gt; XXX
gt; XXX
gt; XXX
gt; XXXX
gt; XXXX
gt; XXXX
gt; XXXX
gt; XXXXX
gt; XXXXX
gt; XXXXX
gt; XXXXX
gt; XXXXX
gt; XXXXXX
gt; XXXXXX
gt; XXXXXX
gt; XXXXXX
gt; XXXXXX
gt; XXXXXX
gt; XXXXXXX

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 ACol BCol C
Rank by
LabelNumbersLabel

X4.03
X2.86
X7.32
X11.71
X3.04
X2.85
XX4.83
XX1.34
XX15.72
XX15.71
XXX10.62
XXX12.11
XXX1.54
XXX0.96
XXX1.35
XXX0.57
XXX1.73
XXXX1.04
XXXX5.61
XXXX5.42
XXXX1.83
XXXXX6.41
XXXXX3.73
XXXXX5.52
XXXXX2.84
XXXXX1.05
XXXXXX3.43
XXXXXX3.81
XXXXXX2.14
XXXXXX1.76
XXXXXX2.05
XXXXXX3.82
XXXXXXX3.51Bravo! Works great. Thanks very much.

Sorry, my mistake. Your formula works perfectly.

Bravissimo! Thanks very much!You're welcome. Thanks for the feedback!

Biff

quot;SteveCquot; gt; wrote in message
...
gt; Sorry, my mistake. Your formula works perfectly.
gt;
gt; Bravissimo! Thanks very much!
gt;