Array Formula - Use of OFFSET function with array argument

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

Hi All,

I am trying to use the OFFSET function with an array argument as
follows:

A1 = "Alan"
A2 = "Bob"

C1 = "Charles"
C2 = "Doug"

This is my formula:

{=OFFSET(A1,0,{0;2},Height,1)}

When I set HEIGHT equal to 2, I was expecting / hoping that it would
return an array as follows:

={"Alan","Bob";"Charles","Doug"}

However, it actually only returns:

={"Alan";"Charles"}

I guess that the height argument is not working in conjunction with
the array argument?

Does anyone know why this is the case, and is there a way around it
(without using a UDF or VBA).

Thanks,

Alan.
 
I am trying to use the OFFSET function with an array argument as
follows:

A1 = "Alan"
A2 = "Bob"

C1 = "Charles"
C2 = "Doug"

This is my formula:

{=OFFSET(A1,0,{0;2},Height,1)}

When I set HEIGHT equal to 2, I was expecting / hoping that it would
return an array as follows:

={"Alan","Bob";"Charles","Doug"}

However, it actually only returns:

={"Alan";"Charles"}

I guess that the height argument is not working in conjunction with
the array argument?

Does anyone know why this is the case, and is there a way around it
(without using a UDF or VBA).
Click to expand...

OFFSET returns Range objects, but when it would return multiple range objects,
as it would using your formula, it's almost as if it returns an array of Range
objects. Whatever it does return, it can only be handled by functions that
specifically expect Range arguments, e.g., CELL, INDEX, N and T. Actually, I
suppose it could also be handled by OFFSET itself, but that way leads to
madness.

Anyway, INDEX(OFFSET(A1,0,{0;2},2,1),1) returns {"Alan";"Charles"} when entered
into a 2 row by 1 column range. If you evaluate it in the formula bar, it
returns only Alan. INDEX(OFFSET(A1,0,{0;2},2,1),2) returns {"Bob";"Doug"} also
when entered into a 2 row by 1 column range. You'd need to use the rather odd
INDEX(OFFSET(A1,0,{0;2},2,1),{1,2}) to return {"Alan","Bob";"Charles","Doug"} to
a 2 row by 2 column range. Note that INDEX only works when returning the results
to a range. This is due to Excel's implicit indexing. I don't fully understand
it, but the developer notes for the Gnome Project's gnumeric spreadsheet include
a discussion.

If you want to use all values at once, then you need to rewrite the OFFSET call
as OFFSET(A1,{0,1},{0;2},1,1), then wrap the result in N if all entries would be
numbers, of in T if all entries would be text. There's no good way to handle
this when entries could be mixed. Given your data, try

=T(OFFSET(A1,{0,1},{0;2},1,1))
 
Harlan Grove said:
OFFSET returns Range objects, but when it would return multiple range
objects, as it would using your formula, it's almost as if it returns
an array of Range objects. Whatever it does return, it can only be
handled by functions that specifically expect Range arguments, e.g.,
CELL, INDEX, N and T. Actually, I suppose it could also be handled by
OFFSET itself, but that way leads to madness.

Anyway, INDEX(OFFSET(A1,0,{0;2},2,1),1) returns {"Alan";"Charles"}
when entered into a 2 row by 1 column range. If you evaluate it in
the formula bar, it returns only Alan.
INDEX(OFFSET(A1,0,{0;2},2,1),2) returns {"Bob";"Doug"} also when
entered into a 2 row by 1 column range. You'd need to use the rather
odd INDEX(OFFSET(A1,0,{0;2},2,1),{1,2}) to return
{"Alan","Bob";"Charles","Doug"} to a 2 row by 2 column range. Note
that INDEX only works when returning the results to a range. This is
due to Excel's implicit indexing. I don't fully understand it, but
the developer notes for the Gnome Project's gnumeric spreadsheet
include a discussion.

If you want to use all values at once, then you need to rewrite the
OFFSET call as OFFSET(A1,{0,1},{0;2},1,1), then wrap the result in N
if all entries would be numbers, of in T if all entries would be
text. There's no good way to handle this when entries could be mixed.
Given your data, try

=T(OFFSET(A1,{0,1},{0;2},1,1))
Click to expand...

Hi Harlan,

Thank you so very much for your response - most enlightening!

I will have a play around with this, but I think you have
fundamentally solved the problem for me.

Thanks again,

Alan.
 
Back
Top