Offset function in Excel 2003

  • Thread starter Thread starter CLarshtnt
  • Start date Start date
School test? Have a look in help, there is ample explanation there


--


Regards,


Peo Sjoblom
 
Offset Fuc is an array function which help you to make change to the
references made dynamically. eg. Suppose you are referring to RAnge A1:b5 for
Product A and you want to change the reference to C1:D5 for Product B you
can do so by changing the Row,Column coordinates in offset function
 
When you need to get more than one row & or One column ie if you put the
height and width as any no more than 1 , then you will have to use offset as
array function.
Anu
 
Of course it depends how you define an array function. There is a
straightway definition for array formulas in Excel Help, but no such
definition exist for array function.

For me, an array function is the one, which behaves like array formula -
i.e. it has several same-dimensional parameters, and returns a value or the
set of values (like SUMPRODUCT), but you enter it without Ctrl+Shif+Enter.
(By your approach, p.e. INDIRECT or even simple range reference are array
functions too.)
 
What is the purpose of the Offset function in Excel 2003?
Click to expand...

Its basic purpose is to find a value within a certain number of rows
or columns from a given cell.

For instance, in one spreadsheet I have a named range consisting of
one cell, called "corner". To the right of this corner, I have five
columns, and below it, five rows, comprising a matrix of unit prices.

1 2 3 4 5
1 $17.51 $32.15 $55.52 $90.56 $130.28
2 $19.25 $33.89 $57.26 $92.30 $132.01
3 $22.05 $36.69 $60.05 $95.11 $134.82
4 $25.83 $40.47 $63.83 $98.88 $138.60
5 $29.15 $43.79 $67.16 $102.20 $141.92

Then I have a large datasheet, which contains the row and column
designation (health level and supervision level for a client's
consumer). If health level is in F4, and supervision level is in G4,
I use this formula to get the rate:

=OFFSET(corner,F4,G4)

Also, as mentioned by other posters, this function can return a range
of more than one cell.
 
I am not talking about array formulas, I am talking about array functions
like TRANSPOSE which is an array function meaning that to use TRANSPOSE only
you need to select multiple cell and enter it with ctrl + shift & enter. In
that sense OFFSET is definitely not an array function.


--


Regards,


Peo Sjoblom
 
Back
Top