range inside RANK formula based on contents of other cells
 

I need a single RANK formula that I can copy to every cell in column P of my
very long sheet (18,000+ rows), which operates on numerical data in column O.
But I really need a sequence of ranks; the first one covers the first 14
rows, the next one covers the next 6 rows, the one after that covers the next
27 rows, etc. The thing that defines those varying ranges is common text in
column K. Those first 14 rows all have "Abstract" in column K, the next 6
all have "Access" in column K, the next 27 all have "Accurate" in column K,
etc. So I am hoping to develop a single Rank formula where the reference is
some kind of function that identifies the common text in column K, and then
offsets to the numerical data in column O. Does that sound possible? Thanks
in advance.

range inside RANK formula based on contents of other cells
 

Hi!

Rank is not a very flexible function. There is no =RANK(IF(.........) type
of formula but there's a way to do it using Sumproduct:

=SUMPRODUCT(--(K$1:K$20=K1),--(O1<O$1:O$20))+1

Copied down as needed.

See this screencap:

http://img142.imageshack.us/img142/3637/rankvq3.jpg

Biff

"andy62" wrote in message
...
I need a single RANK formula that I can copy to every cell in column P of
my
very long sheet (18,000+ rows), which operates on numerical data in column
O.
But I really need a sequence of ranks; the first one covers the first 14
rows, the next one covers the next 6 rows, the one after that covers the
next
27 rows, etc. The thing that defines those varying ranges is common text
in
column K. Those first 14 rows all have "Abstract" in column K, the next 6
all have "Access" in column K, the next 27 all have "Accurate" in column
K,
etc. So I am hoping to develop a single Rank formula where the reference
is
some kind of function that identifies the common text in column K, and
then
offsets to the numerical data in column O. Does that sound possible?
Thanks
in advance.

range inside RANK formula based on contents of other cells
 

Thanks, Biff, that worked great.

(How do I record feedback - the one that gives a helpful post a checkmark?)

"Biff" wrote:

Hi!

Rank is not a very flexible function. There is no =RANK(IF(.........) type
of formula but there's a way to do it using Sumproduct:

=SUMPRODUCT(--(K$1:K$20=K1),--(O1<O$1:O$20))+1

Copied down as needed.

See this screencap:

http://img142.imageshack.us/img142/3637/rankvq3.jpg

Biff

"andy62" wrote in message
...
I need a single RANK formula that I can copy to every cell in column P of
my
very long sheet (18,000+ rows), which operates on numerical data in column
O.
But I really need a sequence of ranks; the first one covers the first 14
rows, the next one covers the next 6 rows, the one after that covers the
next
27 rows, etc. The thing that defines those varying ranges is common text
in
column K. Those first 14 rows all have "Abstract" in column K, the next 6
all have "Access" in column K, the next 27 all have "Accurate" in column
K,
etc. So I am hoping to develop a single Rank formula where the reference
is
some kind of function that identifies the common text in column K, and
then
offsets to the numerical data in column O. Does that sound possible?
Thanks
in advance.

range inside RANK formula based on contents of other cells
 

You're welcome!

Biff

"andy62" wrote in message
...
Thanks, Biff, that worked great.

(How do I record feedback - the one that gives a helpful post a
checkmark?)

"Biff" wrote:

Hi!

Rank is not a very flexible function. There is no =RANK(IF(.........)
type
of formula but there's a way to do it using Sumproduct:

=SUMPRODUCT(--(K$1:K$20=K1),--(O1<O$1:O$20))+1

Copied down as needed.

See this screencap:

http://img142.imageshack.us/img142/3637/rankvq3.jpg

Biff

"andy62" wrote in message
...
I need a single RANK formula that I can copy to every cell in column P
of
my
very long sheet (18,000+ rows), which operates on numerical data in
column
O.
But I really need a sequence of ranks; the first one covers the first
14
rows, the next one covers the next 6 rows, the one after that covers
the
next
27 rows, etc. The thing that defines those varying ranges is common
text
in
column K. Those first 14 rows all have "Abstract" in column K, the
next 6
all have "Access" in column K, the next 27 all have "Accurate" in
column
K,
etc. So I am hoping to develop a single Rank formula where the
reference
is
some kind of function that identifies the common text in column K, and
then
offsets to the numerical data in column O. Does that sound possible?
Thanks
in advance.