OFFSET

  • Thread starter Thread starter John Gregory
  • Start date Start date
J

John Gregory

I read the instructions and though t I understood how to interpret this
function until I read an actual example in use. Here's the way it's written:

=OFFSET(gnp!$A1,B$6-B$9-1,0)
I read part of it to be:

In the workbook called "gnp!", starting with reference cell $A1. and I don't
know how to read "B$6-B$9-1" . It also looks like "height" or "width" is
also missing.

Can anyone offer some guidance please?
 
OFFSET(gnp!$A1,B$6-B$9-1,0)

the above assumes you have numerical values in B6 and B9, the result of
B6-B9 is the offset row wise from A1,
e.g. if B6 is 9 and B9 5 then you would offset 4 rows from A1, then you
subtract 1 from that leaving 3, so the formula assuming the example values
of 9 and 5 will return what's in A4. If height and width are omitted they
will be equal to zero. If height would be 10 then it would return an array
from A3:A13 but the formula as written would return an error
since you need to wrap it in another function, e.g.

=SUM(OFFSET(gnp!$A$1,$B$9-$B$6-1,0,10))

would return the sum of A3:A13

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Peo Sjoblom said:
OFFSET(gnp!$A1,B$6-B$9-1,0)

the above assumes you have numerical values in B6 and B9, the result of
B6-B9 is the offset row wise from A1,
e.g. if B6 is 9 and B9 5 then you would offset 4 rows from A1, then you
subtract 1 from that leaving 3, so the formula assuming the example values
of 9 and 5 will return what's in A4. If height and width are omitted they
will be equal to zero. If height would be 10 then it would return an array
from A3:A13 but the formula as written would return an error
since you need to wrap it in another function, e.g.

=SUM(OFFSET(gnp!$A$1,$B$9-$B$6-1,0,10))

would return the sum of A3:A13
Click to expand...

correction, as I stated first it would be A4 and not A3 so

A4:A13


--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Now I'm puzzled further because the workbook is not returning errors and you
feel it should.... as I did. But sure enough... the darn thing works.

You put me on the right track. I can't explain why I'm not getting an error
message though.

I appreciate the help.
 
Back
Top