ROWS() returns 1?
 

Hello!
I am not an Excel expert and looks like I faced very simple problem.
May be somebody can help me with that,please?

a)I create an array of data in worksheet. Let say:
1 2
3 4
b) I name the cell where the first element is as "first" (Left apper
corner of a worksheet allows to label a particular cell, like
A1-"first")
c) Then I name a new cell with the label "ar" and insert a matrix
formula there {=OFFSET(first,0,0,1,1)}. At this step I assume that my
named cell "ar" represents the array with 2 rows and 2 columns. I also
see the first array value in this cell like "1" (Not a #VALUE, not a
#NAME)
d) If in any cell of the worksheet I'll use the formula =ROWS("ar") it
shows "1". But it should be "2".

What I am missing?

I am using MSExcel 2000 and XP platform. Did I forget to include some
add-ins in the project in order to work with matrix formula?

Thanks a lot,
Sergey

ROWS() returns 1?
 

=OFFSET(first,0,0,1,1) doesn't return an array; see Help for OFFSET.

Alan Beban

Sergey wrote:
Hello!
I am not an Excel expert and looks like I faced very simple problem.
May be somebody can help me with that,please?

a)I create an array of data in worksheet. Let say:
1 2
3 4
b) I name the cell where the first element is as "first" (Left apper
corner of a worksheet allows to label a particular cell, like
A1-"first")
c) Then I name a new cell with the label "ar" and insert a matrix
formula there {=OFFSET(first,0,0,1,1)}. At this step I assume that my
named cell "ar" represents the array with 2 rows and 2 columns. I also
see the first array value in this cell like "1" (Not a #VALUE, not a
#NAME)
d) If in any cell of the worksheet I'll use the formula =ROWS("ar") it
shows "1". But it should be "2".

What I am missing?

I am using MSExcel 2000 and XP platform. Did I forget to include some
add-ins in the project in order to work with matrix formula?

Thanks a lot,
Sergey

ROWS() returns 1?
 

Alan,
Thank you for your reply.
You are correct when pointing me that I set a width and height in
OFFSET function to 1. I should not expect 2 rows after that. But
unfortunately this problem stays even if I increase the width and
height to 2 or more, like that:
{=OFFSET(start,0,0,2,2)}
The rows' count function ROWS(ar) still returns 1.
In reality I have much bigger array then I use in my question and row
and heights were set up correctly there.

One more thing: I use [ctrl]+[shift]+[enter] in all cases when I am
inserting functions.
I'll be very obliged if you can show me an example of how ROWS
function returns the correct result.

Thank you,
Sergey


Alan Beban wrote in message ...
=OFFSET(first,0,0,1,1) doesn't return an array; see Help for OFFSET.

Alan Beban

Sergey wrote:
Hello!
I am not an Excel expert and looks like I faced very simple problem.
May be somebody can help me with that,please?

a)I create an array of data in worksheet. Let say:
1 2
3 4
b) I name the cell where the first element is as "first" (Left apper
corner of a worksheet allows to label a particular cell, like
A1-"first")
c) Then I name a new cell with the label "ar" and insert a matrix
formula there {=OFFSET(first,0,0,1,1)}. At this step I assume that my
named cell "ar" represents the array with 2 rows and 2 columns. I also
see the first array value in this cell like "1" (Not a #VALUE, not a
#NAME)
d) If in any cell of the worksheet I'll use the formula =ROWS("ar") it
shows "1". But it should be "2".

What I am missing?

I am using MSExcel 2000 and XP platform. Did I forget to include some
add-ins in the project in order to work with matrix formula?

Thanks a lot,
Sergey