Problems with using TEXT, INDIRECT and ADDRESS within an array for

[Philip Hunt]

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
"0000000000000000100000000000000010000000001000000000000000000". 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)),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.
 
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())),(ADDRESS((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

 

[Bob Phillips]

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)

 

[David Biddulph]

I'm not sure about the formula, but aren't there a number of duplicate
parentheses?
Wouldn't
=SUM(IF(ISERROR(FIND($B751,$E4:$BL4)),0,1))
be the same as
=SUM((IF((ISERROR((FIND($B751,$E4:$BL4)))),0,1))) ?
--
David Biddulph

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)

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
"0000000000000000100000000000000010000000001000000000000000000". 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)),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.

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())),(ADDRESS((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

 

[Harlan Grove]

The array formula {=SUM((IF((ISERROR((FIND(B751,E4:BL4)))),0,1)))} givesme
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.  Thereare
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
"0000000000000000100000000000000010000000001000000000000000000".

....

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.

 

[Philip Mark Hunt]

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

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)

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
"0000000000000000100000000000000010000000001000000000000000000". 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)),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.

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())),(ADDRESS((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

 

[Philip Mark Hunt]

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

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
"0000000000000000100000000000000010000000001000000000000000000".

....

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.

 

[Philip Mark Hunt]

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

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

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
"0000000000000000100000000000000010000000001000000000000000000".

....

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.

 

[Philip Mark Hunt]

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

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

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

....
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
"0000000000000000100000000000000010000000001000000000000000000".
....

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.

 

[Philip Mark Hunt]

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

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

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

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


:

....
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
"0000000000000000100000000000000010000000001000000000000000000".
....

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.

 

[Bob Phillips]

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)

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

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)

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
"0000000000000000100000000000000010000000001000000000000000000". 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)),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.

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())),(ADDRESS((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

 

[Philip Mark Hunt]

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

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
"0000000000000000100000000000000010000000001000000000000000000".

....

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.