I am using the basic Excel help tool within my 2002 version to investigate
how arrays operate. Specifically, I started with the ROW() function. The
Excel help says that if you put, ROW() into a cell, I used C4, then the row
number should appear and that does work. In the second example it says that
if you put ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows below the
formula should populate with 4, 5, 6. What happens when I try this is that I
only get a 4 displayed in the cell, C4, where the formula is. The other
cells below are left blank. I tried doing the exact same thing in Open
Office Calc and it worked perfectly. Is there some setting or feature that
needs to be changed or added in Excel? By the way, the array curly brackets
do get placed around the formula after I hit Ctrl-Shift-Enter.

Thanks for your reply.

--
David Farber
Los Osos, CA

David Farber pretended :
I am using the basic Excel help tool within my 2002 version to investigate
how arrays operate. Specifically, I started with the ROW() function. The
Excel help says that if you put, ROW() into a cell, I used C4, then the row
number should appear and that does work. In the second example it says that
if you put ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows below the
formula should populate with 4, 5, 6. What happens when I try this is that I
only get a 4 displayed in the cell, C4, where the formula is. The other cells
below are left blank. I tried doing the exact same thing in Open Office Calc
and it worked perfectly. Is there some setting or feature that needs to be
changed or added in Excel? By the way, the array curly brackets do get placed
around the formula after I hit Ctrl-Shift-Enter.

Thanks for your reply.

Formulas *always* must begin with *=* sign

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:
David Farber pretended :
I am using the basic Excel help tool within my 2002 version to
investigate how arrays operate. Specifically, I started with the
ROW() function. The Excel help says that if you put, ROW() into a
cell, I used C4, then the row number should appear and that does
work. In the second example it says that if you put ROW(C4:D6) and
then use the Ctrl-Shift-Enter, the rows below the formula should
populate with 4, 5, 6. What happens when I try this is that I only
get a 4 displayed in the cell, C4, where the formula is. The other
cells below are left blank. I tried doing the exact same thing in
Open Office Calc and it worked perfectly. Is there some setting or
feature that needs to be changed or added in Excel? By the way, the
array curly brackets do get placed around the formula after I hit
Ctrl-Shift-Enter. Thanks for your reply.

Formulas *always* must begin with *=* sign

Yes, you are correct. I did put in the equals sign in the spreadsheet. For
some reason, I thought that would have been implied in my question based on
my results but I should have known better. Still looking for the answer.

Thanks for your reply.

--
David Farber
Los Osos, CA

David Farber formulated the question :
GS wrote:
David Farber pretended :
I am using the basic Excel help tool within my 2002 version to
investigate how arrays operate. Specifically, I started with the
ROW() function. The Excel help says that if you put, ROW() into a
cell, I used C4, then the row number should appear and that does
work. In the second example it says that if you put ROW(C4:D6) and
then use the Ctrl-Shift-Enter, the rows below the formula should
populate with 4, 5, 6. What happens when I try this is that I only
get a 4 displayed in the cell, C4, where the formula is. The other
cells below are left blank. I tried doing the exact same thing in
Open Office Calc and it worked perfectly. Is there some setting or
feature that needs to be changed or added in Excel? By the way, the
array curly brackets do get placed around the formula after I hit
Ctrl-Shift-Enter. Thanks for your reply.

Formulas *always* must begin with *=* sign

Yes, you are correct. I did put in the equals sign in the spreadsheet. For
some reason, I thought that would have been implied in my question based on
my results but I should have known better. Still looking for the answer.

Thanks for your reply.

Array formulas can be entered in a *single* cell then *copied* to any
other cells that you want to use the array formula in, *OR* you can
select a contiguous range of cells to enter the same formula in all the
selected cells. The difference in behavior is as follows...

When I *select A1:A3* and enter "=C4:D6" as an array formula, those 3
cells populate with 4,5,6 respectively. These cells all ref "C4:D6".

If you enter the same array formula in B1 (single cell) and copy it
down to B2/B3, those 3 cells populate with the same results (4,5,6
respectively) *but* the refs change to "C5:D7" in B2 and "C6:D8" in B3.

I suspect that you did not select the 2 cells that you were expecting
"5,6" to populate. Note that formulas can not change other cells, only
the cells that contains the formula. So if C5/C6 are empty then your
formula did not populate them when you did Ctrl+Shift+Enter.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:
David Farber formulated the question :
GS wrote:
David Farber pretended :
I am using the basic Excel help tool within my 2002 version to
investigate how arrays operate. Specifically, I started with the
ROW() function. The Excel help says that if you put, ROW() into a
cell, I used C4, then the row number should appear and that does
work. In the second example it says that if you put ROW(C4:D6) and
then use the Ctrl-Shift-Enter, the rows below the formula should
populate with 4, 5, 6. What happens when I try this is that I only
get a 4 displayed in the cell, C4, where the formula is. The other
cells below are left blank. I tried doing the exact same thing in
Open Office Calc and it worked perfectly. Is there some setting or
feature that needs to be changed or added in Excel? By the way, the
array curly brackets do get placed around the formula after I hit
Ctrl-Shift-Enter. Thanks for your reply.

Formulas *always* must begin with *=* sign

Yes, you are correct. I did put in the equals sign in the
spreadsheet. For some reason, I thought that would have been implied
in my question based on my results but I should have known better.
Still looking for the answer. Thanks for your reply.

Array formulas can be entered in a *single* cell then *copied* to any
other cells that you want to use the array formula in, *OR* you can
select a contiguous range of cells to enter the same formula in all
the selected cells. The difference in behavior is as follows...

When I *select A1:A3* and enter "=C4:D6" as an array formula, those 3
cells populate with 4,5,6 respectively. These cells all ref "C4:D6".

If you enter the same array formula in B1 (single cell) and copy it
down to B2/B3, those 3 cells populate with the same results (4,5,6
respectively) *but* the refs change to "C5:D7" in B2 and "C6:D8" in
B3.
I suspect that you did not select the 2 cells that you were expecting
"5,6" to populate. Note that formulas can not change other cells, only
the cells that contains the formula. So if C5/C6 are empty then your
formula did not populate them when you did Ctrl+Shift+Enter.

Starting with a blank worksheet, when I selected cells A1:A3 and entered
"=C4:D6" as an array formula, those 3 cells populate with zeroes. Was I
supposed to use the ROW() function there? If I do, I at least get a 4 to
appear in A1. The others are still zeroes.

When I entered the same array formula in B1 (single cell) and copied it down
to B2/B3, those 3 cells populate with zeroes. The refs did change to "C5:D7"
in B2 and "C6:D8" in B3. Same question as above regarding the ROW()
function.

Note that formulas can not change other cells, only
the cells that contains the formula. So if C5/C6 are empty then your
formula did not populate them when you did Ctrl+Shift+Enter.

I agree with you about the part that C5/C6 are empty but I'm not sure what
conclusion you have come to about that. Could something be amiss with the
way Excel is interpreting my "Ctrl-Shift-Enter?"

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

David Farber wrote:
GS wrote:
David Farber formulated the question :
GS wrote:
David Farber pretended :
I am using the basic Excel help tool within my 2002 version to
investigate how arrays operate. Specifically, I started with the
ROW() function. The Excel help says that if you put, ROW() into a
cell, I used C4, then the row number should appear and that does
work. In the second example it says that if you put ROW(C4:D6) and
then use the Ctrl-Shift-Enter, the rows below the formula should
populate with 4, 5, 6. What happens when I try this is that I only
get a 4 displayed in the cell, C4, where the formula is. The other
cells below are left blank. I tried doing the exact same thing in
Open Office Calc and it worked perfectly. Is there some setting or
feature that needs to be changed or added in Excel? By the way,
the array curly brackets do get placed around the formula after I
hit Ctrl-Shift-Enter. Thanks for your reply.

Formulas *always* must begin with *=* sign

Yes, you are correct. I did put in the equals sign in the
spreadsheet. For some reason, I thought that would have been implied
in my question based on my results but I should have known better.
Still looking for the answer. Thanks for your reply.

Array formulas can be entered in a *single* cell then *copied* to any
other cells that you want to use the array formula in, *OR* you can
select a contiguous range of cells to enter the same formula in all
the selected cells. The difference in behavior is as follows...

When I *select A1:A3* and enter "=C4:D6" as an array formula, those 3
cells populate with 4,5,6 respectively. These cells all ref "C4:D6".

If you enter the same array formula in B1 (single cell) and copy it
down to B2/B3, those 3 cells populate with the same results (4,5,6
respectively) *but* the refs change to "C5:D7" in B2 and "C6:D8" in
B3.
I suspect that you did not select the 2 cells that you were expecting
"5,6" to populate. Note that formulas can not change other cells,
only the cells that contains the formula. So if C5/C6 are empty then
your formula did not populate them when you did Ctrl+Shift+Enter.

Starting with a blank worksheet, when I selected cells A1:A3 and
entered "=C4:D6" as an array formula, those 3 cells populate with
zeroes. Was I supposed to use the ROW() function there? If I do, I at
least get a 4 to appear in A1. The others are still zeroes.

When I entered the same array formula in B1 (single cell) and copied
it down to B2/B3, those 3 cells populate with zeroes. The refs did
change to "C5:D7" in B2 and "C6:D8" in B3. Same question as above
regarding the ROW() function.

Note that formulas can not change other cells, only
the cells that contains the formula. So if C5/C6 are empty then your
formula did not populate them when you did Ctrl+Shift+Enter.

I agree with you about the part that C5/C6 are empty but I'm not sure
what conclusion you have come to about that. Could something be amiss
with the way Excel is interpreting my "Ctrl-Shift-Enter?"

Thanks for your reply.

Ok, I re-read your instructions again. When I preselect the range (and use
the ROW() function) before I enter the formula, then it works. And that
goes to your comment that it cannot change the contents of cells that aren't
selected. I hope I interpreted that correctly.

I guess the Open Office version works under a different set of rules.

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

"David Farber" wrote:
The Excel help says that if you put, ROW() into a cell,
I used C4, then the row number should appear and that
does work. In the second example it says that if you put
ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows
below the formula should populate with 4, 5, 6. What
happens when I try this is that I only get a 4 displayed
in the cell, C4, where the formula is. The other cells
below are left blank.

You probably overlooked the subtle instructions. According to the Help
page, after you normally-enter (just press Enter as usual) the formula into
A2, the instructions ask you to select A2:A4, press F2, then press
ctrl+shift+Enter to array-enter the formula.

But please note: that method is nonsensical.

Sometimes the help pages show you how to do things in order to demonstrate
form or method. That does not mean it is the best way to accomplish the
task.

First, it is nonsensical to use ROW(C4:D6) in that context. Since we are
entering the formula into a single column, the ROW parameter should be a
single column, e.g. ROW(C4:C6).

Second, it is nonsensical to refer to another column unnecessarily, C4:C6 in
this case. And that goes double for original range C4:D6.

The problem that causes is: it creates a dependency on those cells. So
whenever any of C4:C6 is modified, A2:A4 will be recalculated.

Sometimes that is our intent. And even if it isn't, it is not a big deal in
this very simple case.

But generally, it could be a big deal when ROW(C4:C6) is part of a more
complicated and time-consuming formula, e.g. a lookup operation.

So ROW(A4:A6) would be a better choice in this context.

Finally, it is nonsensical to use an array-entered formula at all in this
particular example. Simply normally-enter =ROW(A4) into A2, then copy the
formula down through A6.

I avoid array-entered formulas, especially multi-cell array-entered
formulas. They are difficult to modify.

For example, if you had array-entered =ROW(A4:A6) into A2:A4, then tried to
replace the formula in A2 with =ROW(A4) as I suggested, you probably
encountered an error to the effect "cannot change part of an array".

To avoid the error, we must first select A2:A4 and delete the formula.

"joeu2004" wrote in message
...
"David Farber" wrote:
The Excel help says that if you put, ROW() into a cell,
I used C4, then the row number should appear and that
does work. In the second example it says that if you put
ROW(C4:D6) and then use the Ctrl-Shift-Enter, the rows
below the formula should populate with 4, 5, 6. What
happens when I try this is that I only get a 4 displayed
in the cell, C4, where the formula is. The other cells
below are left blank.

You probably overlooked the subtle instructions. According to the Help
page, after you normally-enter (just press Enter as usual) the formula
into A2, the instructions ask you to select A2:A4, press F2, then press
ctrl+shift+Enter to array-enter the formula.

But please note: that method is nonsensical.

Sometimes the help pages show you how to do things in order to demonstrate
form or method. That does not mean it is the best way to accomplish the
task.

First, it is nonsensical to use ROW(C4:D6) in that context. Since we are
entering the formula into a single column, the ROW parameter should be a
single column, e.g. ROW(C4:C6).

Second, it is nonsensical to refer to another column unnecessarily, C4:C6
in this case. And that goes double for original range C4:D6.

The problem that causes is: it creates a dependency on those cells. So
whenever any of C4:C6 is modified, A2:A4 will be recalculated.

Sometimes that is our intent. And even if it isn't, it is not a big deal
in this very simple case.

But generally, it could be a big deal when ROW(C4:C6) is part of a more
complicated and time-consuming formula, e.g. a lookup operation.

So ROW(A4:A6) would be a better choice in this context.

Finally, it is nonsensical to use an array-entered formula at all in this
particular example. Simply normally-enter =ROW(A4) into A2, then copy the
formula down through A6.

I avoid array-entered formulas, especially multi-cell array-entered
formulas. They are difficult to modify.

For example, if you had array-entered =ROW(A4:A6) into A2:A4, then tried
to replace the formula in A2 with =ROW(A4) as I suggested, you probably
encountered an error to the effect "cannot change part of an array".

To avoid the error, we must first select A2:A4 and delete the formula.

I think the help section intended the example to be more instructional than
practically useful. Let me just finish this part of my question by saying
that the function works perfectly well in Open Office's Calc. See image
he http://webpages.charter.net/mrfixite...rayExample.jpg Maybe we
can sidestep this whole discussion and let me explain why I am even
researching this topic. I was interested in converting a Word document which
contained a contact list into Excel format. This list was to be used in a
mail merge to print labels. Long story short, I imported the list into Excel
and that worked fine except the City State and Zip were all included in one
field. I found many ways to break this into separate fields/columns but the
method I liked most required commas between the city and state and the data
document didn't have commas inserted in those positions. So I found this
link with this formula at :
http://www.excelforum.com/showthread.php?t=342639&p=876198&viewfull=1#post87 6198=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 and how I couldmodify it to search for any digit, 0-9 within the CityStateZip string. Ithought I would be able to locate where the zip code began in the string andsubsequently I could count spaces backward to find the state and then thecity. This could all be accomplished because there are no cities or stateswhich contain numbers in their names.Thanks for your reply.--David FarberLos Osos, CA