| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help w/ INDIRECT
I get "No Show" everytime for the following:
=IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
Hard to tell what you're trying to do but the error is generated if any cell
is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
It could also be caused by any cell in that range having an error value. But
since the OP specified "No Show" when all columns are blank in X33:IV33, I assume that was the purpose of the ISERROR in the original formula and not because those cells can actually evaluate to errors. "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
Your array formula seems to be going in the right direction. $X$26:$IV$26
are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there
any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
X33:IV33 are attendance hours (ie 2.5), but some users like to type "E" for
excused. If a student does not attend class, it is left blank. "T. Valko" wrote: Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Please help w/ INDIRECT
Try this array formula:
=IF(COUNT(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,ISNU MBER(X33:IV33),0)),"No Show") Format as DATE Biff "St@cy" wrote in message ... X33:IV33 are attendance hours (ie 2.5), but some users like to type "E" for excused. If a student does not attend class, it is left blank. "T. Valko" wrote: Ok, so what's in X33:IV33? Is it text or numbers or a mix of both? Are there any formulas in that range that return blanks? Biff "St@cy" wrote in message ... Your array formula seems to be going in the right direction. $X$26:$IV$26 are dates. I want the formula to return the first date a student attends class and "no show" if they have not recieve any attendance hours (X33:IV33). "T. Valko" wrote: Hard to tell what you're trying to do but the error is generated if any cell is empty in this range: X33:IV33 This will cause the MIN function to return 0 which is then passed to ADDRESS as the column number arguemnt and it evaluates to an invalid address: =ADDRESS(26,0) This causes ISERROR to evaluate to TRUE resulting in "No Show". If you're wanting to return the value from X26:IV26 that corresponds to the first non-empty cell in X33:IV33 try this array formula** : =IF(COUNTA(X33:IV33),INDEX(X26:IV26,MATCH(TRUE,X33 :IV33<"",0)),"No Show") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "St@cy" wrote in message ... I get "No Show" everytime for the following: =IF(ISERROR(INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV3 3)*(X33:$IV33<"")))))=TRUE,"No Show",INDIRECT(ADDRESS(26,MIN(COLUMN(X33:IV33)*(X3 3:$IV33<""))))) I have an INDIRECT function that works for MAX, but I now want to use MIN. I need a way to return "No Show" if all columns are blank in columns X:IV. Please help me fix this function. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
| INDIRECT.EXT | Excel Worksheet Functions | |||
| INDIRECT.EXT | Excel Worksheet Functions | |||
| indirect using name | Excel Worksheet Functions | |||
| INDIRECT Help | Excel Worksheet Functions | |||
Hybrid Mode
