| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problems with using TEXT, INDIRECT and ADDRESS within an array for
PROBLEMS WITH USING TEXT, INDIRECT AND ADDRESS WITHIN AN ARRAY FORMULA
Basic problem The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives me my expected value of 3. I need, though, to have a formula of this format across 60 columns x 2000 rows =120,000 cells. The search value changes for each row, and the row in the search area changes for each column. There are therefore in fact 120,000 similar formula calculations required, but they are slightly different in each case. I have therefore tried to turn this into an array formula, storing it with a name and then just copying =[Name] to each of the other 119,999 cells. I cannot though get the function to work even once and I am at my wit's end after five evenings of trying. As an extension of the above what I really want is not the SUM but in fact the 'string' of zeroes and ones that the arrayed ISERROR produces to be placed in the cell as a text string. I have tried =TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))," 0") but all I get is zero, rather than the desired "0000000000000000100000000000000010000000001000000 000000000000". I have also tried it with the format string being a string of 60 zeroes, but that produces the same 'bad' result. Facts established towards 'building of formula' {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} works as I want it to. (INDIRECT("$B"&ROW())) gives me the result B751 when placed in row 751. =(ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),5,4,1 )&":"&ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),6 4,4,1)) gives me the text answer E4:BL4 when placed in the column that has the number 4 in its cell in row number 249. €ƒ BUT when placing these 'tested' elements in their appropriate places in the formula, in replacement of B751 and E4:BL4, as follows €“ {=SUM((IF((ISERROR((FIND((INDIRECT("$B"&ROW())),(A DDRESS((INDIRECT("R249C"&(COLUMN(),FALSE)),5,4,1)& ":"&ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),64, 4,1)))))),0,1)))} the formula does not work. I look forward to comments and advice from the user community. Best regards Philip Medina, Kwinana, Perth, Western Australia |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problems with using TEXT, INDIRECT and ADDRESS within an array for
|
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problems with using TEXT, INDIRECT and ADDRESS within an array
Dear Bob
Your way of writing the working one and mine are effectively the same; I jusy have a personal style of using a few more brackets, as I have been a computer programmer for a,long time and that was in my original training. The statement that works is not the problem.. The problem is the conversion of that into a'standard' formula, rather than having many score, if not hundreds or thousands of variants to cover the subtle differences in values required for each of the 120,000 cells. I read about aray formulas in a Platinum Edition Que Guided and thought it would be a solution to my need but it is proving to be more difficult than I had hoped. Regards Philip -- Graewood Business Services, Kwinana, Perth, Western Australia "Bob Phillips" wrote: Shouldn't it just be =SUM((IF((ISERROR((FIND($B751,$E4:$BL4)))),0,1))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Philip Hunt" <Philip wrote in message ... PROBLEMS WITH USING TEXT, INDIRECT AND ADDRESS WITHIN AN ARRAY FORMULA Basic problem The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives me my expected value of 3. I need, though, to have a formula of this format across 60 columns x 2000 rows =120,000 cells. The search value changes for each row, and the row in the search area changes for each column. There are therefore in fact 120,000 similar formula calculations required, but they are slightly different in each case. I have therefore tried to turn this into an array formula, storing it with a name and then just copying =[Name] to each of the other 119,999 cells. I cannot though get the function to work even once and I am at my wit's end after five evenings of trying. As an extension of the above what I really want is not the SUM but in fact the 'string' of zeroes and ones that the arrayed ISERROR produces to be placed in the cell as a text string. I have tried =TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))," 0") but all I get is zero, rather than the desired "0000000000000000100000000000000010000000001000000 000000000000". I have also tried it with the format string being a string of 60 zeroes, but that produces the same 'bad' result. Facts established towards 'building of formula' {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} works as I want it to. (INDIRECT("$B"&ROW())) gives me the result B751 when placed in row 751. =(ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),5,4,1 )&":"&ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),6 4,4,1)) gives me the text answer E4:BL4 when placed in the column that has the number 4 in its cell in row number 249. BUT when placing these 'tested' elements in their appropriate places in the formula, in replacement of B751 and E4:BL4, as follows - {=SUM((IF((ISERROR((FIND((INDIRECT("$B"&ROW())),(A DDRESS((INDIRECT("R249C"&(COLUMN(),FALSE)),5,4,1)& ":"&ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),64, 4,1)))))),0,1)))} the formula does not work. I look forward to comments and advice from the user community. Best regards Philip Medina, Kwinana, Perth, Western Australia |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problems with using TEXT, INDIRECT and ADDRESS within an array
It wasn't the removal of brackets that was my main point, but rather the
addition of the $ to fix the column letters. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Philip Mark Hunt" wrote in message ... Dear Bob Your way of writing the working one and mine are effectively the same; I jusy have a personal style of using a few more brackets, as I have been a computer programmer for a,long time and that was in my original training. The statement that works is not the problem.. The problem is the conversion of that into a'standard' formula, rather than having many score, if not hundreds or thousands of variants to cover the subtle differences in values required for each of the 120,000 cells. I read about aray formulas in a Platinum Edition Que Guided and thought it would be a solution to my need but it is proving to be more difficult than I had hoped. Regards Philip -- Graewood Business Services, Kwinana, Perth, Western Australia "Bob Phillips" wrote: Shouldn't it just be =SUM((IF((ISERROR((FIND($B751,$E4:$BL4)))),0,1))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Philip Hunt" <Philip wrote in message ... PROBLEMS WITH USING TEXT, INDIRECT AND ADDRESS WITHIN AN ARRAY FORMULA Basic problem The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives me my expected value of 3. I need, though, to have a formula of this format across 60 columns x 2000 rows =120,000 cells. The search value changes for each row, and the row in the search area changes for each column. There are therefore in fact 120,000 similar formula calculations required, but they are slightly different in each case. I have therefore tried to turn this into an array formula, storing it with a name and then just copying =[Name] to each of the other 119,999 cells. I cannot though get the function to work even once and I am at my wit's end after five evenings of trying. As an extension of the above what I really want is not the SUM but in fact the 'string' of zeroes and ones that the arrayed ISERROR produces to be placed in the cell as a text string. I have tried =TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))," 0") but all I get is zero, rather than the desired "0000000000000000100000000000000010000000001000000 000000000000". I have also tried it with the format string being a string of 60 zeroes, but that produces the same 'bad' result. Facts established towards 'building of formula' {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} works as I want it to. (INDIRECT("$B"&ROW())) gives me the result B751 when placed in row 751. =(ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),5,4,1 )&":"&ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),6 4,4,1)) gives me the text answer E4:BL4 when placed in the column that has the number 4 in its cell in row number 249. BUT when placing these 'tested' elements in their appropriate places in the formula, in replacement of B751 and E4:BL4, as follows - {=SUM((IF((ISERROR((FIND((INDIRECT("$B"&ROW())),(A DDRESS((INDIRECT("R249C"&(COLUMN(),FALSE)),5,4,1)& ":"&ADDRESS((INDIRECT("R249C"&COLUMN(),FALSE)),64, 4,1)))))),0,1)))} the formula does not work. I look forward to comments and advice from the user community. Best regards Philip Medina, Kwinana, Perth, Western Australia |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problems with using TEXT, INDIRECT and ADDRESS within an arrayfor
Philip Hunt <Philip wrote...
.... The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives me my expected value of 3. *I need, though, to have a formula of this format across 60 columns x 2000 rows =120,000 cells. *The search value changes for each row, and the row in the search area changes for each column. *There are therefore in fact 120,000 similar formula calculations required, but they are slightly different in each case. . . . .... As an extension of the above what I really want is not the SUM but in fact the 'string' of zeroes and ones that the arrayed ISERROR produces to be placed in the cell as a text string. *I have tried =TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1))), "0") but all I get is zero, rather than the desired "000000000000000010000000000000001000000000100000 0000000000000". .... Unfortunately, the correct result is just "0" or "1". Your IF(..) expression returns an *ARRAY* of numbers, e.g., {0,0,0,0,1,0,1,0,0,0,...}, and passing that through the TEXT function only converts the array of numbers into an array of strings {"0","0","0","0","1","0","1","0","0","0",...}. Excel will only display one entry from either the array of numbers or the array of strings in a given single cell. You'd need an add-in function to concatenate all the 0s and 1s into a single string. Facts established towards 'building of formula' [reformatted - removing unnecessary braces and parenteses] =SUM( IF( ISERROR( FIND( B751, E4:BL4 ) ), 0, 1 ) ) works as I want it to. .... So would =COUNT(FIND(B751,E4:BL4)) [reformatted - removing unnecessary parentheses] =ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) gives me the text answer E4:BL4 when placed in the column that has the number 4 in its cell in row number 249. If you really want the text reference, you could reduce this to =MID(CELL("Address",($A$1,OFFSET($E$1,D249-1,0,1,60))),6,32) for the cell in column D. BUT when placing these 'tested' elements in their appropriate places in the formula, in replacement of B751 and E4:BL4, as follows – [reformatted - yada yada yada] =SUM( IF( ISERROR( FIND( INDIRECT("$B"&ROW()), ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) ) ), 0, 1 ) ) the formula does not work. You forgot to wrap the ADDRESS(..)&":"&ADDRESS(..) inside it's own INDIRECT call. It's just literal text without that INDIRECT call, so the FIND call is going to treat it as a single text string rather than refer to the cells in the range for which it's the text reference. This could be simplified substantially. It's unclear where your formulas are in relation to the cells in row 249 that you're accessing, but it looks to me like an array formula of the form =COUNT(FIND($B<row,OFFSET($E$1,<col$249-1,0,1,60))) would return the proper result. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problems with using TEXT, INDIRECT and ADDRESS within an array
Dear Harlan
Thank you for your input regarding the wrapping of the ADDRESS group in its own INDIRECT call. Unfortunately that did not solve the problem. As explained in the original query, the SUM or COUNT is not the real end result I need. I need the string of ones and zeroes reflecting the ISERROR result on the FIND. I will try in an EXTREMELY!! reduced form to set out the data I have, and the task I am trying to achieve. The top rows and left columns are the Excel Column Letters and Row Numbers. E F G H I 4 001C,056D 5 025E,056D 068H The above is the data area. B BV BW BX BY 249 4 5 6 7 751 001C 10000 1056 056D 10000 01000 1275 025E 01000 2068 068H 00010 The above is the 'results' area. The values in Column B and Row 249 are literals in the spreadsheet, set there to drive the formula. Column B provides the search value, while the values in Row 249 give the row number to be 'examined' to give the formula result in columns BV thru BY and rows 751, 1056, 1275 and 2068. Please note that in the real version there are actually 2000 rows by 49 columns of formula results needed. I have shown the desired final text result in BV751, BV1056, BW1056, BW1275, and BW2068. Regarding creating the string from the generated 0s and 1s, you say that I would " need an add-in function to concatenate all the 0s and 1s into a single string". Are you referring to CONCATENATE or some special add-in, of the kind I have seen mentioned in other threads on this forum, as available from 3rd party websites? If it is the latter could you please direct me to an appropriate source. I note the comments re all the brackets. It is just my style; I like to put the 'data' that is being passed to a function in its own set of brackets. I look forward to further comments and advice from the user community. Best regards Philip Medina, Kwinana, Perth, Western Australia -- Graewood Business Services, Kwinana, Perth, Western Australia "Harlan Grove" wrote: Philip Hunt <Philip wrote... .... The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives me my expected value of 3. I need, though, to have a formula of this format across 60 columns x 2000 rows =120,000 cells. The search value changes for each row, and the row in the search area changes for each column. There are therefore in fact 120,000 similar formula calculations required, but they are slightly different in each case. . . . .... As an extension of the above what I really want is not the SUM but in fact the 'string' of zeroes and ones that the arrayed ISERROR produces to be placed in the cell as a text string. I have tried =TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1))), "0") but all I get is zero, rather than the desired "000000000000000010000000000000001000000000100000 0000000000000". .... Unfortunately, the correct result is just "0" or "1". Your IF(..) expression returns an *ARRAY* of numbers, e.g., {0,0,0,0,1,0,1,0,0,0,...}, and passing that through the TEXT function only converts the array of numbers into an array of strings {"0","0","0","0","1","0","1","0","0","0",...}. Excel will only display one entry from either the array of numbers or the array of strings in a given single cell. You'd need an add-in function to concatenate all the 0s and 1s into a single string. Facts established towards 'building of formula' [reformatted - removing unnecessary braces and parenteses] =SUM( IF( ISERROR( FIND( B751, E4:BL4 ) ), 0, 1 ) ) works as I want it to. .... So would =COUNT(FIND(B751,E4:BL4)) [reformatted - removing unnecessary parentheses] =ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) gives me the text answer E4:BL4 when placed in the column that has the number 4 in its cell in row number 249. If you really want the text reference, you could reduce this to =MID(CELL("Address",($A$1,OFFSET($E$1,D249-1,0,1,60))),6,32) for the cell in column D. BUT when placing these 'tested' elements in their appropriate places in the formula, in replacement of B751 and E4:BL4, as follows €“ [reformatted - yada yada yada] =SUM( IF( ISERROR( FIND( INDIRECT("$B"&ROW()), ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) ) ), 0, 1 ) ) the formula does not work. You forgot to wrap the ADDRESS(..)&":"&ADDRESS(..) inside it's own INDIRECT call. It's just literal text without that INDIRECT call, so the FIND call is going to treat it as a single text string rather than refer to the cells in the range for which it's the text reference. This could be simplified substantially. It's unclear where your formulas are in relation to the cells in row 249 that you're accessing, but it looks to me like an array formula of the form =COUNT(FIND($B<row,OFFSET($E$1,<col$249-1,0,1,60))) would return the proper result. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problems with using TEXT, INDIRECT and ADDRESS within an array
Hello Harlan
I am sorry but the limits of this bulletin board meant that my attempt to give the data I am working with, which looks great in an Excel spreadsheet or Word table, has turned out to be very difficult to understand on here. The data area I have shown consists of Rows 4 and 5, and Columns E,F,G,H, and I. E4 = "001C,056D"; F5 = "025E,056D"; H5 = "068H" The results area shown consists of five rows and five columns; I have used the row numbers and columns as they appear on the original LARGE spreadsheet; that is why they are so spread out. Row 249 has in columns BV thru BY the sequence 4,5,6,7. B751 = "001C"; B1056 = "056D"; B1275 = "025E"; B2068 = "068H"; The desired formula results are as follows €“ BV751 = "10000"; BV1056 = "10000"; BW1056 = "01000"; BW1275 = "01000"; BW2068 = "00010" I hope this clarifies the immediately previous post. Best regards Philip Medina, Kwinana, Perth, Western Australia -- Graewood Business Services, Kwinana, Perth, Western Australia "Philip Mark Hunt" wrote: Dear Harlan Thank you for your input regarding the wrapping of the ADDRESS group in its own INDIRECT call. Unfortunately that did not solve the problem. As explained in the original query, the SUM or COUNT is not the real end result I need. I need the string of ones and zeroes reflecting the ISERROR result on the FIND. I will try in an EXTREMELY!! reduced form to set out the data I have, and the task I am trying to achieve. The top rows and left columns are the Excel Column Letters and Row Numbers. E F G H I 4 001C,056D 5 025E,056D 068H The above is the data area. B BV BW BX BY 249 4 5 6 7 751 001C 10000 1056 056D 10000 01000 1275 025E 01000 2068 068H 00010 The above is the 'results' area. The values in Column B and Row 249 are literals in the spreadsheet, set there to drive the formula. Column B provides the search value, while the values in Row 249 give the row number to be 'examined' to give the formula result in columns BV thru BY and rows 751, 1056, 1275 and 2068. Please note that in the real version there are actually 2000 rows by 49 columns of formula results needed. I have shown the desired final text result in BV751, BV1056, BW1056, BW1275, and BW2068. Regarding creating the string from the generated 0s and 1s, you say that I would " need an add-in function to concatenate all the 0s and 1s into a single string". Are you referring to CONCATENATE or some special add-in, of the kind I have seen mentioned in other threads on this forum, as available from 3rd party websites? If it is the latter could you please direct me to an appropriate source. I note the comments re all the brackets. It is just my style; I like to put the 'data' that is being passed to a function in its own set of brackets. I look forward to further comments and advice from the user community. Best regards Philip Medina, Kwinana, Perth, Western Australia -- Graewood Business Services, Kwinana, Perth, Western Australia "Harlan Grove" wrote: Philip Hunt <Philip wrote... .... The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives me my expected value of 3. I need, though, to have a formula of this format across 60 columns x 2000 rows =120,000 cells. The search value changes for each row, and the row in the search area changes for each column. There are therefore in fact 120,000 similar formula calculations required, but they are slightly different in each case. . . . .... As an extension of the above what I really want is not the SUM but in fact the 'string' of zeroes and ones that the arrayed ISERROR produces to be placed in the cell as a text string. I have tried =TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1))), "0") but all I get is zero, rather than the desired "000000000000000010000000000000001000000000100000 0000000000000". .... Unfortunately, the correct result is just "0" or "1". Your IF(..) expression returns an *ARRAY* of numbers, e.g., {0,0,0,0,1,0,1,0,0,0,...}, and passing that through the TEXT function only converts the array of numbers into an array of strings {"0","0","0","0","1","0","1","0","0","0",...}. Excel will only display one entry from either the array of numbers or the array of strings in a given single cell. You'd need an add-in function to concatenate all the 0s and 1s into a single string. Facts established towards 'building of formula' [reformatted - removing unnecessary braces and parenteses] =SUM( IF( ISERROR( FIND( B751, E4:BL4 ) ), 0, 1 ) ) works as I want it to. .... So would =COUNT(FIND(B751,E4:BL4)) [reformatted - removing unnecessary parentheses] =ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) gives me the text answer E4:BL4 when placed in the column that has the number 4 in its cell in row number 249. If you really want the text reference, you could reduce this to =MID(CELL("Address",($A$1,OFFSET($E$1,D249-1,0,1,60))),6,32) for the cell in column D. BUT when placing these 'tested' elements in their appropriate places in the formula, in replacement of B751 and E4:BL4, as follows €“ [reformatted - yada yada yada] =SUM( IF( ISERROR( FIND( INDIRECT("$B"&ROW()), ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) ) ), 0, 1 ) ) the formula does not work. You forgot to wrap the ADDRESS(..)&":"&ADDRESS(..) inside it's own INDIRECT call. It's just literal text without that INDIRECT call, so the FIND call is going to treat it as a single text string rather than refer to the cells in the range for which it's the text reference. This could be simplified substantially. It's unclear where your formulas are in relation to the cells in row 249 that you're accessing, but it looks to me like an array formula of the form =COUNT(FIND($B<row,OFFSET($E$1,<col$249-1,0,1,60))) would return the proper result. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problems with using TEXT, INDIRECT and ADDRESS within an array
Hello Harlan
Further work has shown that your INDIRECT suggestion DOES seem to solve that part of the formula. The problem now seems to reside with the setting of the initial search value. While INDIRECT("$B"&ROW()) standing on its own correctly picks up the "001C" search value, its is quite different when in the array formula. The result returned under evaluate for that first bit of the formula evaluation is VALUE#. That of course means that at the end, even though the address resolving etc. is correct, the answer is still zero, because there is an error at the start of the formula evaluation. Your further suggestions would be most welcome. Thank you so very much for already resolving the correct use of ADDRESS and INDIRECT in the building of the address where FIND is to search. Best regards Philip Medina, Kwinana, Perth, Western Australia -- Graewood Business Services, Kwinana, Perth, Western Australia "Philip Mark Hunt" wrote: Hello Harlan I am sorry but the limits of this bulletin board meant that my attempt to give the data I am working with, which looks great in an Excel spreadsheet or Word table, has turned out to be very difficult to understand on here. The data area I have shown consists of Rows 4 and 5, and Columns E,F,G,H, and I. E4 = "001C,056D"; F5 = "025E,056D"; H5 = "068H" The results area shown consists of five rows and five columns; I have used the row numbers and columns as they appear on the original LARGE spreadsheet; that is why they are so spread out. Row 249 has in columns BV thru BY the sequence 4,5,6,7. B751 = "001C"; B1056 = "056D"; B1275 = "025E"; B2068 = "068H"; The desired formula results are as follows €“ BV751 = "10000"; BV1056 = "10000"; BW1056 = "01000"; BW1275 = "01000"; BW2068 = "00010" I hope this clarifies the immediately previous post. Best regards Philip Medina, Kwinana, Perth, Western Australia -- Graewood Business Services, Kwinana, Perth, Western Australia "Philip Mark Hunt" wrote: Dear Harlan Thank you for your input regarding the wrapping of the ADDRESS group in its own INDIRECT call. Unfortunately that did not solve the problem. As explained in the original query, the SUM or COUNT is not the real end result I need. I need the string of ones and zeroes reflecting the ISERROR result on the FIND. I will try in an EXTREMELY!! reduced form to set out the data I have, and the task I am trying to achieve. The top rows and left columns are the Excel Column Letters and Row Numbers. E F G H I 4 001C,056D 5 025E,056D 068H The above is the data area. B BV BW BX BY 249 4 5 6 7 751 001C 10000 1056 056D 10000 01000 1275 025E 01000 2068 068H 00010 The above is the 'results' area. The values in Column B and Row 249 are literals in the spreadsheet, set there to drive the formula. Column B provides the search value, while the values in Row 249 give the row number to be 'examined' to give the formula result in columns BV thru BY and rows 751, 1056, 1275 and 2068. Please note that in the real version there are actually 2000 rows by 49 columns of formula results needed. I have shown the desired final text result in BV751, BV1056, BW1056, BW1275, and BW2068. Regarding creating the string from the generated 0s and 1s, you say that I would " need an add-in function to concatenate all the 0s and 1s into a single string". Are you referring to CONCATENATE or some special add-in, of the kind I have seen mentioned in other threads on this forum, as available from 3rd party websites? If it is the latter could you please direct me to an appropriate source. I note the comments re all the brackets. It is just my style; I like to put the 'data' that is being passed to a function in its own set of brackets. I look forward to further comments and advice from the user community. Best regards Philip Medina, Kwinana, Perth, Western Australia -- Graewood Business Services, Kwinana, Perth, Western Australia "Harlan Grove" wrote: Philip Hunt <Philip wrote... .... The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives me my expected value of 3. I need, though, to have a formula of this format across 60 columns x 2000 rows =120,000 cells. The search value changes for each row, and the row in the search area changes for each column. There are therefore in fact 120,000 similar formula calculations required, but they are slightly different in each case. . . . .... As an extension of the above what I really want is not the SUM but in fact the 'string' of zeroes and ones that the arrayed ISERROR produces to be placed in the cell as a text string. I have tried =TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1))), "0") but all I get is zero, rather than the desired "000000000000000010000000000000001000000000100000 0000000000000". .... Unfortunately, the correct result is just "0" or "1". Your IF(..) expression returns an *ARRAY* of numbers, e.g., {0,0,0,0,1,0,1,0,0,0,...}, and passing that through the TEXT function only converts the array of numbers into an array of strings {"0","0","0","0","1","0","1","0","0","0",...}. Excel will only display one entry from either the array of numbers or the array of strings in a given single cell. You'd need an add-in function to concatenate all the 0s and 1s into a single string. Facts established towards 'building of formula' [reformatted - removing unnecessary braces and parenteses] =SUM( IF( ISERROR( FIND( B751, E4:BL4 ) ), 0, 1 ) ) works as I want it to. .... So would =COUNT(FIND(B751,E4:BL4)) [reformatted - removing unnecessary parentheses] =ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) gives me the text answer E4:BL4 when placed in the column that has the number 4 in its cell in row number 249. If you really want the text reference, you could reduce this to =MID(CELL("Address",($A$1,OFFSET($E$1,D249-1,0,1,60))),6,32) for the cell in column D. BUT when placing these 'tested' elements in their appropriate places in the formula, in replacement of B751 and E4:BL4, as follows €“ [reformatted - yada yada yada] =SUM( IF( ISERROR( FIND( INDIRECT("$B"&ROW()), ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) ) ), 0, 1 ) ) the formula does not work. You forgot to wrap the ADDRESS(..)&":"&ADDRESS(..) inside it's own INDIRECT call. It's just literal text without that INDIRECT call, so the FIND call is going to treat it as a single text string rather than refer to the cells in the range for which it's the text reference. This could be simplified substantially. It's unclear where your formulas are in relation to the cells in row 249 that you're accessing, but it looks to me like an array formula of the form =COUNT(FIND($B<row,OFFSET($E$1,<col$249-1,0,1,60))) would return the proper result. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problems with using TEXT, INDIRECT and ADDRESS within an array
Hello Harlan
I have found the solution to the search value problem. I used the CELL function to force the pickup of the contents. I still, please, need your help with the function to rather than SUM my array of 1s and 0s, instead to convert it into a single cell string of character 1s and 0s. I look forward to your answer. Best regards Philip Medina, Kwinana, Perth, Western Australia -- Graewood Business Services, Kwinana, Perth, Western Australia "Philip Mark Hunt" wrote: Hello Harlan Further work has shown that your INDIRECT suggestion DOES seem to solve that part of the formula. The problem now seems to reside with the setting of the initial search value. While INDIRECT("$B"&ROW()) standing on its own correctly picks up the "001C" search value, its is quite different when in the array formula. The result returned under evaluate for that first bit of the formula evaluation is VALUE#. That of course means that at the end, even though the address resolving etc. is correct, the answer is still zero, because there is an error at the start of the formula evaluation. Your further suggestions would be most welcome. Thank you so very much for already resolving the correct use of ADDRESS and INDIRECT in the building of the address where FIND is to search. Best regards Philip Medina, Kwinana, Perth, Western Australia -- Graewood Business Services, Kwinana, Perth, Western Australia "Philip Mark Hunt" wrote: Hello Harlan I am sorry but the limits of this bulletin board meant that my attempt to give the data I am working with, which looks great in an Excel spreadsheet or Word table, has turned out to be very difficult to understand on here. The data area I have shown consists of Rows 4 and 5, and Columns E,F,G,H, and I. E4 = "001C,056D"; F5 = "025E,056D"; H5 = "068H" The results area shown consists of five rows and five columns; I have used the row numbers and columns as they appear on the original LARGE spreadsheet; that is why they are so spread out. Row 249 has in columns BV thru BY the sequence 4,5,6,7. B751 = "001C"; B1056 = "056D"; B1275 = "025E"; B2068 = "068H"; The desired formula results are as follows €“ BV751 = "10000"; BV1056 = "10000"; BW1056 = "01000"; BW1275 = "01000"; BW2068 = "00010" I hope this clarifies the immediately previous post. Best regards Philip Medina, Kwinana, Perth, Western Australia -- Graewood Business Services, Kwinana, Perth, Western Australia "Philip Mark Hunt" wrote: Dear Harlan Thank you for your input regarding the wrapping of the ADDRESS group in its own INDIRECT call. Unfortunately that did not solve the problem. As explained in the original query, the SUM or COUNT is not the real end result I need. I need the string of ones and zeroes reflecting the ISERROR result on the FIND. I will try in an EXTREMELY!! reduced form to set out the data I have, and the task I am trying to achieve. The top rows and left columns are the Excel Column Letters and Row Numbers. E F G H I 4 001C,056D 5 025E,056D 068H The above is the data area. B BV BW BX BY 249 4 5 6 7 751 001C 10000 1056 056D 10000 01000 1275 025E 01000 2068 068H 00010 The above is the 'results' area. The values in Column B and Row 249 are literals in the spreadsheet, set there to drive the formula. Column B provides the search value, while the values in Row 249 give the row number to be 'examined' to give the formula result in columns BV thru BY and rows 751, 1056, 1275 and 2068. Please note that in the real version there are actually 2000 rows by 49 columns of formula results needed. I have shown the desired final text result in BV751, BV1056, BW1056, BW1275, and BW2068. Regarding creating the string from the generated 0s and 1s, you say that I would " need an add-in function to concatenate all the 0s and 1s into a single string". Are you referring to CONCATENATE or some special add-in, of the kind I have seen mentioned in other threads on this forum, as available from 3rd party websites? If it is the latter could you please direct me to an appropriate source. I note the comments re all the brackets. It is just my style; I like to put the 'data' that is being passed to a function in its own set of brackets. I look forward to further comments and advice from the user community. Best regards Philip Medina, Kwinana, Perth, Western Australia -- Graewood Business Services, Kwinana, Perth, Western Australia "Harlan Grove" wrote: Philip Hunt <Philip wrote... .... The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives me my expected value of 3. I need, though, to have a formula of this format across 60 columns x 2000 rows =120,000 cells. The search value changes for each row, and the row in the search area changes for each column. There are therefore in fact 120,000 similar formula calculations required, but they are slightly different in each case. . . . .... As an extension of the above what I really want is not the SUM but in fact the 'string' of zeroes and ones that the arrayed ISERROR produces to be placed in the cell as a text string. I have tried =TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1))), "0") but all I get is zero, rather than the desired "000000000000000010000000000000001000000000100000 0000000000000". .... Unfortunately, the correct result is just "0" or "1". Your IF(..) expression returns an *ARRAY* of numbers, e.g., {0,0,0,0,1,0,1,0,0,0,...}, and passing that through the TEXT function only converts the array of numbers into an array of strings {"0","0","0","0","1","0","1","0","0","0",...}. Excel will only display one entry from either the array of numbers or the array of strings in a given single cell. You'd need an add-in function to concatenate all the 0s and 1s into a single string. Facts established towards 'building of formula' [reformatted - removing unnecessary braces and parenteses] =SUM( IF( ISERROR( FIND( B751, E4:BL4 ) ), 0, 1 ) ) works as I want it to. .... So would =COUNT(FIND(B751,E4:BL4)) [reformatted - removing unnecessary parentheses] =ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) gives me the text answer E4:BL4 when placed in the column that has the number 4 in its cell in row number 249. If you really want the text reference, you could reduce this to =MID(CELL("Address",($A$1,OFFSET($E$1,D249-1,0,1,60))),6,32) for the cell in column D. BUT when placing these 'tested' elements in their appropriate places in the formula, in replacement of B751 and E4:BL4, as follows €“ [reformatted - yada yada yada] =SUM( IF( ISERROR( FIND( INDIRECT("$B"&ROW()), ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) ) ), 0, 1 ) ) the formula does not work. You forgot to wrap the ADDRESS(..)&":"&ADDRESS(..) inside it's own INDIRECT call. It's just literal text without that INDIRECT call, so the FIND call is going to treat it as a single text string rather than refer to the cells in the range for which it's the text reference. This could be simplified substantially. It's unclear where your formulas are in relation to the cells in row 249 that you're accessing, but it looks to me like an array formula of the form =COUNT(FIND($B<row,OFFSET($E$1,<col$249-1,0,1,60))) would return the proper result. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Problems with using TEXT, INDIRECT and ADDRESS within an array
Dear Harlan
Thank you for your crucial input re the ADDRESS wrapping. I have now found my solution to this step of my spreadsheet problem. I do now have a new problem but if I don't find an answer in the archive of answers on this forum I will raise a new thread. Best regards Philip -- Graewood Business Services, Kwinana, Perth, Western Australia "Harlan Grove" wrote: Philip Hunt <Philip wrote... .... The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} gives me my expected value of 3. I need, though, to have a formula of this format across 60 columns x 2000 rows =120,000 cells. The search value changes for each row, and the row in the search area changes for each column. There are therefore in fact 120,000 similar formula calculations required, but they are slightly different in each case. . . . .... As an extension of the above what I really want is not the SUM but in fact the 'string' of zeroes and ones that the arrayed ISERROR produces to be placed in the cell as a text string. I have tried =TEXT(((IF((ISERROR((FIND(B751,E4:BL4)))),0,1))), "0") but all I get is zero, rather than the desired "000000000000000010000000000000001000000000100000 0000000000000". .... Unfortunately, the correct result is just "0" or "1". Your IF(..) expression returns an *ARRAY* of numbers, e.g., {0,0,0,0,1,0,1,0,0,0,...}, and passing that through the TEXT function only converts the array of numbers into an array of strings {"0","0","0","0","1","0","1","0","0","0",...}. Excel will only display one entry from either the array of numbers or the array of strings in a given single cell. You'd need an add-in function to concatenate all the 0s and 1s into a single string. Facts established towards 'building of formula' [reformatted - removing unnecessary braces and parenteses] =SUM( IF( ISERROR( FIND( B751, E4:BL4 ) ), 0, 1 ) ) works as I want it to. .... So would =COUNT(FIND(B751,E4:BL4)) [reformatted - removing unnecessary parentheses] =ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) gives me the text answer E4:BL4 when placed in the column that has the number 4 in its cell in row number 249. If you really want the text reference, you could reduce this to =MID(CELL("Address",($A$1,OFFSET($E$1,D249-1,0,1,60))),6,32) for the cell in column D. BUT when placing these 'tested' elements in their appropriate places in the formula, in replacement of B751 and E4:BL4, as follows €“ [reformatted - yada yada yada] =SUM( IF( ISERROR( FIND( INDIRECT("$B"&ROW()), ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 5, 4, 1 ) &":" &ADDRESS( INDIRECT("R249C"&COLUMN(),FALSE), 64, 4, 1 ) ) ), 0, 1 ) ) the formula does not work. You forgot to wrap the ADDRESS(..)&":"&ADDRESS(..) inside it's own INDIRECT call. It's just literal text without that INDIRECT call, so the FIND call is going to treat it as a single text string rather than refer to the cells in the range for which it's the text reference. This could be simplified substantially. It's unclear where your formulas are in relation to the cells in row 249 that you're accessing, but it looks to me like an array formula of the form =COUNT(FIND($B<row,OFFSET($E$1,<col$249-1,0,1,60))) would return the proper result. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
| Using SUM, ADDRESS, INDIRECT in an array not working | Excel Worksheet Functions | |||
| using INDIRECT(ADDRESS(...)) | Excel Discussion (Misc queries) | |||
| Indirect(Address(... | Excel Discussion (Misc queries) | |||
| Array reference using indirect address | Excel Worksheet Functions | |||
Hybrid Mode
