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