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