I'm offset by the Offset Function

[Hari]

Hi,

In the Excel's help Offset function is explained in the 2nd Example as

=SUM(OFFSET(C3:E5,-1,0,3,3)) will give a result of "Sums the range C2:E4"

My doubt is would it matter if we specify the first argument as C3:E5 or C3
only.

As per my perfunctory reading thru help it seemed that even if I write
=SUM(OFFSET(C3,-1,0,3,3)) we should get the result as "Sums the range C2:E4"
(As per my understanding fourth and fifth arguments "Height" and "Width"
shud take care of the required range. Shouldnt it?)

Also if the above is true then why is it listed in the first argument
description that it is "Reference must refer to a cell or range of adjacent
cells"

Hence, my question is how would the results of Offset change when our first
argument is a cell and on the other case argument is a range of adjacent
cells?

Regards,
Hari
India

PS: It seems Microsoft's help is missing the example data for the offset
function in Excel 2002

 

[Frank Kabel]

Hi
your formula is valid but in the first case you could either write it
as:
=SUM(OFFSET(C3,-1,0,3,3))

or
=SUM(OFFSET(C3:E5,-1,0))

 

[Hari]

Hi Frank,

Does this mean that if we specify a "reference" in the first argument then
we neednt specify the 4th and the 5th argument (or they may be zero and
zero) which are height and width? If so why has MS given a "confusing"
example in help. I mean is there anything behind it.

Also suppose we specify a single cell in 1st argument then we need to
specify the 4th and 5th as non zero in case we want the final offset answer
to be a range?

Regards,
Hari
India

 

[Earl Kiosterud]

Hari,

The first argument is always a cell reference. The 4th and 5th arguments
are the size of the range returned by OFFSET, and have nothing to do with
the size of the first arg, unless you don't supply them, in which case the
size and shape of the returned (offset) range is the size and shape of the
first argument.

 

[Hari]

Hi Earl,

Thanx a lot.

Now it seems OFFSET has become a subset of understandable set.

Regards,
Hari
India