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.