offset formula

  • Thread starter Thread starter mangesh
  • Start date Start date
M

mangesh

hi
what is offset formula and how does it work
if you can explain me with an example it will be better for me t
unerstand i
 
Hi
first: have you lokked at Excel's help :-)
Offset returns a range which is shifted and resized according to its
parameters:
=OFFSET(start_range,row,column,[height],[width])

e.g.
=OFFSET($A$1,2,3)
shifgts the cell reference from A1 two rows down and three rows to the
right. so the above would return $D$3
 
=offset(reference,rows,cols,[height],[width])

It takes a cell reference as a starting point, eg say cell B4, and then lets you
offset from that starting point by any number of rows and columns. There are
also two optional arguments at the end to allow you specify a range of x rows or
y columns from that offset point, eg:-

=OFFSET(B4,1,1) gives you what's in C5
=OFFSET(B4,2,1) gives you what's in C6
=OFFSET(B4,3,1) gives you what's in C7

=OFFSET(B4,1,1) gives you what's in C5
=OFFSET(B4,1,2) gives you what's in D5
=OFFSET(B4,1,3) gives you what's in E5

=OFFSET(B4,1,1,5) gives you a range of C5:C9
=OFFSET(B4,1,1,6) gives you a range of C5:C10
=OFFSET(B4,1,1,7) gives you a range of C5:C11

=OFFSET(B4,1,1,0,5) gives you a range of C5:G5
=OFFSET(B4,1,1,0,6) gives you a range of C5:H5
=OFFSET(B4,1,1,0,7) gives you a range of C5:I5

Note, the 0s are there for explanation only and can be left out as long as you
have the commas, eg

=OFFSET(B4,1,1,,5) gives you a range of C5:G5

You can also use all arguments and get a 2D range, eg:-

=OFFSET(B4,1,1,3,5) gives you a range of C5:G7

With the ranges, you can then use them in say a SUM function, eg:-

=SUM(OFFSET(B4,1,1,0,5)) gives you the sum of all values in the range C5:G5
 
Back
Top