OFFSET

R

Roger H

When is it useful to use OFFSET function.
I'm not sure I understand exactly what it's for but think it may be useful!?
 
B

bj

the offset is useful for simplifying formulas when you want to define a range
for different purposes when the only one cell in the range is easily
identified.
for example
you might have a worksheet with a block of information about an individual
with the name in just the upper left cell in the block .the offset() could be
used to get information relative to the location of the name.

Because the offset can define a range, it can be used inside lookup()
formulas. or othe formulas
 
A

Arvi Laanemets

Hi

1. To return a value from cell a fixed number of rows and cells apart from
base cell, like
=OFFSET($A$1,1,4)
(the example returns value from cell E2)

2. To return a range of fixed dimension which starts a fixed number of rows
and cells apart from base cell, like
=SUM(OFFSET($A$1,1,4,3,1))
(the example sums values from range E2:E4)
NB! When using OFFSET to determine a range, it MUST be a part of a formula
which requires a range there. Or you use it in named range definition
(dynamic ranges).
 
Top