OFFSET Function - what is it doing?

[Guest]

Hi,

I'm trying to implement some functionality using the OFFSET function, but do
not understand what the two optional variables are doing.

For example, I am happy that for the range below, OFFSET(A1,1,2) will return
the value "c2"
A B c
1 a1 b1 c1
2 a2 b2 c2
3 a3 b3 c3

If I made the function OFFSET(A1,1,2,1,1) c2 is also returned but any other
values fior the two optional variables seem to return some really strange
results - usually REF or VALUE errrors.

The online XL help describes these as values as the height or width that the
returned reference is to be, but I don't understand what this means. Is the
function trying to return a range of values to a single cell or is it doing
something else?

There also seem sto be some very strange behaviour associated with the
physical location of the function in 2003 XL (I haven't been able to
duplicate it in 2007) where if I try to get the number of rows/columns to be
an entire single row/column and the function is placed on that row/column it
always returns a 0. If the function is cut-and-pasted to another location not
on that row/column and then back again it works! Is this some undocumented
feature of '03 XL?

ANy help would be apprciated.

TIA

Dave

 

[Guest]

Hi Dave,

The last 2 parameters return the height in rows and the width in columns.
You would only use these if you wanted to return a range in a formula rather
than a single cell. For example in the vlookup function you have a range
where the data is to be found. (Not that I would necessarily use offset in
such a formula but it could be used.)

Regards,

OssieMac

 

[Earl Kiosterud]

To add to that, when the last two arguments are > 1, you're asking OFFSET to return more
than one cell, so you normally need something that takes in the resulting array and gives
one result, something like:

=SUM( OFFSET(A1,1,2,2,1) )
=MAX( OFFSET(A1,1,2,2,1) )

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------