joeu2004 wrote:
"David Farber" wrote:
In my original message I said, "...I tried doing the exact
same thing in Open Office Calc and it worked perfectly. "

Ah, yes. I went looking for such a reference before posting, but I
missed it.

To be clear: you are indeed asking about Microsoft Excel.

I'm not sure what the mystery still is.

I pointed to the subtle instructions in the ROW help page. You can
enter =ROW(C4:D6) into C4 (although =ROW(C4:C6) makes more sense for
your purposes), then select C4:C6, press F2, then press
ctrl+shift+Enter.
Or with forethought, you can select C4:C46, type =ROW(C4:D6), then
press ctrl+shift+Enter.

As I noted before, I suspect you simply typed =ROW(C4:D6) in C4, then
pressed ctrl+shift+Enter. Of course, at that point, Excel does not
know how many rows to propagate the array formula into. That is why
you see only 4 in C4.

I would be surprised the Apache Open Office spreadsheet (whatever it
is called) knows any better either, when you make the same mistake. But
anything is possible in software.

Be that as it may, I don't see how this exercise (and unnecessary way
to accomplish the same result with ROW per se) helps with your
original problem or curiosity. To wit....


"David Farber" wrote elsewhere (edited):
I found this link with this formula at :
=MID(A5,SEARCH(",",A1)+2,MIN(SEARCH({0,1,2,3,4,5,6 ,7,8,9},A1&"0123456789"))
-2-(SEARCH(",",A1)+1))....confirmed with CONTROL+SHIFT+ENTER.
I was curious to know how that expression was evaluated

Since SEARCH takes only a single-valued expression for the first
parameter, array-entering the formula cause the evaluation of the 2nd
SEARCH expression to create an array. In effect, it is as you had
written (if we could, which we cannot):

MIN({SEARCH(0,A1&"0123456789"),SEARCH(1,A1&"012345 6789"),...,SEARCH(9,A1&"0123456789")})

By appending "0123456789", the SEARCH will always succeed. That is
needed so that MIN does not propagate the Excel error that SEARCH
would return if it failed.

Thus, MIN sees an array of indexes (character positions) in
A1&"0123456789". By returning the smallest such index, MIN returns
the position of the first number in A1, if any.

Caveat: Microsoft Excel is inconsistent with where it permits the
use such "array expressions". For example,
SUMPRODUCT(VLOOKUP(A1:A10,Table,2)) should be evaluated as if we had
written
SUMPRODUCT({VLOOKUP(A1,Table,2),VLOOKUP(A2,Table,2 ),...,VLOOKUP(A10,Table,2)),
returning the sum of the 10 lookup results. But it does not :-(.
The point is: Always test any array-entered formula to be sure it is
doing what you intended. And avoid them when you can.

Finally, I think we're on the same wavelength now. The initial mystery is
mostly solved. That was that I needed to highlight the cells where the data
was going to be displayed before entering the formula. The other part of the
mystery was how the other software, Apache's Calc, was able to do this
without prompting. For now, I'm just going to concentrate on Excel.

The reason I chose to use this as a starting point to figure out arrays was
it seemed like a very simple example of how arrays are created. I didn't
expect that I would be writing several lengthy messages trying to figure it
out.

Now back to the big picture. I like how you explained this:
Since SEARCH takes only a single-valued expression for the first
parameter, array-entering the formula cause the evaluation of the 2nd
SEARCH expression to create an array. In effect, it is as you had
written (if we could, which we cannot):

MIN({SEARCH(0,A1&"0123456789"),SEARCH(1,A1&"012345 6789"),...,SEARCH(9,A1&"0123456789")})

Now my question is, which I have been unable to figure out by
experimentation, what is the correct result of:
SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") assuming for example that A1
contains a city, state, and zip? You said it would produce an array. That
makes perfect sense to me but do I have to select a group of cells as before
and then enter the formula? And how should it be entered into the formula
box? Do I use Ctrl-Shift-Enter to add braces even though there are already
braces around the search text? Every way I try it, I can only get a single
number returned. I was hoping a nice array would have been produced
somewhere.

Just to be perfectly clear, I chose as my city, state, zip field, "anytown,
ca, 91234" and the search result returns 19 which is where the first 0
appears in the concatenated string, "anytown, ca 91234012345679"

Thanks for your reply.
--
David Farber
Los Osos, CA