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

Hi!

Try this in column C:

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

Biff

"SteveC" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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 <[email protected]>,
SteveC <[email protected]> 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

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

Bravo! Works great. Thanks very much.

Sorry, my mistake. Your formula works perfectly.

Bravissimo! Thanks very much!

You're welcome. Thanks for the feedback!

Biff

"SteveC" <[email protected]> wrote in message
news:[email protected]...
> Sorry, my mistake. Your formula works perfectly.
>
> Bravissimo! Thanks very much!
>