range inside RANK formula based on contents of other cells

[Guest]

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.

 

[Biff]

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

 

[Guest]

Thanks, Biff, that worked great.

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

 

[Biff]

You're welcome!

Biff

Thanks, Biff, that worked great.

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