Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Cell Address of MIN value - array formula
Hey
The following formula returns a numeric(percent) value: =MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ")*('PDAs Report'! H3:H1000=L9),'PDAs Report'!J3:J1000)) In order for the numeric values to even be evaluated, the following 2 conditions must be met: The value in range G3:G1000 must contain "BJ ". The numeric value in range H3:H1000 MUST be greater than the numeric value in cell L9. The formula then returns the minimum value from all the values that met the above criteria. My question is how can I make it return the actual cell address instead of the value of the cell? Can you please provide me a few methods to reach the desired result? Thanks Todd |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Cell Address of MIN value - array formula
wrote...
.... The following formula returns a numeric(percent) value: =MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ")*('PDAs Report'! H3:H1000=L9),'PDAs Report'!J3:J1000)) .... My question is how can I make it return the actual cell address instead of the value of the cell? .... Try the array formula =SUBSTITUTE(CELL("Address",INDEX('PDAs Report'!J3:J1000, MATCH(MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ") *('PDAs Report'!H3:H1000=L9),'PDAs Report'!J3:J1000)), 'PDAs Report'!J3:J1000,0))),"$","") If you don't want the workbook/worksheet name included, try the array formula =ADDRESS(MATCH(MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ") *('PDAs Report'!H3:H1000=C1),'PDAs Report'!J3:J1000)), 'PDAs Report'!J3:J1000,0)+2,10,4) Note that this is specific to your 'PDAs Report'!J3:J1000 range (the +2 adjusts the MATCH result to the correct row number, and 10 is the column index of column J). The last argument, 4, specifies fully relative cell address. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Cell Address of MIN value - array formula
The "easy" way is to use your current formula and then refer to that
formula: Assume your current formula is in A1. Then, this array formula: ="J"&MATCH(1,(LEFT(G3:G10,3)="BJ ")*(H3:H10=L9)*(J3:J10=A1),0)+2 If you want it all in a single formula then in the above formula replace this: (J3:J10=A1) with your current formula. By the time you add the sheet names it's gonna be a real mess! -- Biff Microsoft Excel MVP wrote in message ups.com... Hey The following formula returns a numeric(percent) value: =MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ")*('PDAs Report'! H3:H1000=L9),'PDAs Report'!J3:J1000)) In order for the numeric values to even be evaluated, the following 2 conditions must be met: The value in range G3:G1000 must contain "BJ ". The numeric value in range H3:H1000 MUST be greater than the numeric value in cell L9. The formula then returns the minimum value from all the values that met the above criteria. My question is how can I make it return the actual cell address instead of the value of the cell? Can you please provide me a few methods to reach the desired result? Thanks Todd |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Cell Address of MIN value - array formula
"Harlan Grove" wrote in message
ps.com... wrote... ... The following formula returns a numeric(percent) value: =MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ")*('PDAs Report'! H3:H1000=L9),'PDAs Report'!J3:J1000)) ... My question is how can I make it return the actual cell address instead of the value of the cell? ... Try the array formula =SUBSTITUTE(CELL("Address",INDEX('PDAs Report'!J3:J1000, MATCH(MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ") *('PDAs Report'!H3:H1000=L9),'PDAs Report'!J3:J1000)), 'PDAs Report'!J3:J1000,0))),"$","") If you don't want the workbook/worksheet name included, try the array formula =ADDRESS(MATCH(MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ") *('PDAs Report'!H3:H1000=C1),'PDAs Report'!J3:J1000)), 'PDAs Report'!J3:J1000,0)+2,10,4) Note that this is specific to your 'PDAs Report'!J3:J1000 range (the +2 adjusts the MATCH result to the correct row number, and 10 is the column index of column J). The last argument, 4, specifies fully relative cell address. I didn't try the first formula but the second formula doesn't account for duplicate MIN's not meeting the conditions. Surely you tested for that? <g -- Biff Microsoft Excel MVP |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Cell Address of MIN value - array formula
"T. Valko" wrote...
"Harlan Grove" wrote in message .... =ADDRESS(MATCH(MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ") *('PDAs Report'!H3:H1000=C1),'PDAs Report'!J3:J1000)), 'PDAs Report'!J3:J1000,0)+2,10,4) .... I didn't try the first formula but the second formula doesn't account for duplicate MIN's not meeting the conditions. So you don't fix formulas? That's OK. I'll continue to fix yours. =ADDRESS(MATCH(MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ") *('PDAs Report'!H3:H1000=C1),'PDAs Report'!J3:J1000)), IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ")*('PDAs Report'!H3:H1000=C1), 'PDAs Report'!J3:J1000),0)+2,10,4) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Cell Address of MIN value - array formula
"Harlan Grove" wrote in message
... "T. Valko" wrote... "Harlan Grove" wrote in message ... =ADDRESS(MATCH(MIN(IF((LEFT('PDAs Report'!G3:G1000,3)="BJ ") *('PDAs Report'!H3:H1000=C1),'PDAs Report'!J3:J1000)), 'PDAs Report'!J3:J1000,0)+2,10,4) ... I didn't try the first formula but the second formula doesn't account for duplicate MIN's not meeting the conditions. So you don't fix formulas? That's OK. I'll continue to fix yours. That's how I learn! -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
USING VLOOKUP TO RETURN A CELL ADDRESS | Excel Worksheet Functions | |||
LOOKUP & RETURN CELL ADDRESS | Excel Worksheet Functions | |||
V Lookup and return cell address | Excel Worksheet Functions | |||
How do I use a function to return the address of a cell? | Excel Worksheet Functions | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions |