Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What formula is used for subtracting a range of different cells f. | Excel Worksheet Functions | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Range of cells and division formula | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions |