Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I use both RANK and MATCH
Hi, I have a data table with say 12 columns (A-M) and 12 rows (1-12) of data.
I have create a formula as follows to extract one number depending on my search criteria. OFFSET('DataTable'!$A$3,MATCH($A$3,' DataTable '!$A$4:$A$12,0),MATCH(C11,' DataTable '!$B$2:$FE$2,0)) My question is, can I modify this formula to give the rank of a certain column. Ie in column D is the third number down RANK 1 in terms of greatest value? RANK('52 L'!D3,'52 L'!D1:M12) Many thanks for any advice. Andy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I use both RANK and MATCH
Would you like to (a) explain the first formula in more detail - its
purpose, and (b) give more detail on the last question. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Andy the yeti" wrote in message ... Hi, I have a data table with say 12 columns (A-M) and 12 rows (1-12) of data. I have create a formula as follows to extract one number depending on my search criteria. OFFSET('DataTable'!$A$3,MATCH($A$3,' DataTable '!$A$4:$A$12,0),MATCH(C11,' DataTable '!$B$2:$FE$2,0)) My question is, can I modify this formula to give the rank of a certain column. Ie in column D is the third number down RANK 1 in terms of greatest value? RANK('52 L'!D3,'52 L'!D1:M12) Many thanks for any advice. Andy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I use both RANK and MATCH
Hi,
The first formula works as a double lookup returning a single value based on two search criteria, I would ideally like to develop this to search for a column of data within the table and then apply a =RANK formula to it. Hope this helps and sorry for the confusion. "Bernard Liengme" wrote: Would you like to (a) explain the first formula in more detail - its purpose, and (b) give more detail on the last question. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Andy the yeti" wrote in message ... Hi, I have a data table with say 12 columns (A-M) and 12 rows (1-12) of data. I have create a formula as follows to extract one number depending on my search criteria. OFFSET('DataTable'!$A$3,MATCH($A$3,' DataTable '!$A$4:$A$12,0),MATCH(C11,' DataTable '!$B$2:$FE$2,0)) My question is, can I modify this formula to give the rank of a certain column. Ie in column D is the third number down RANK 1 in terms of greatest value? RANK('52 L'!D3,'52 L'!D1:M12) Many thanks for any advice. Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Rank & Match Problem | Excel Worksheet Functions | |||
Rank - Specific match only | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |