Offset formulae

[montycarlo]

Hi,

would like to know abt the offset function, that can we move data with the help of offset formula horizontally instead of vertically (as you have mentioned in the dynamic dashboard template).


any help much appreciated.


Thanks,

Gaurav

 

[Hui]

Gaurav

The Offset formula is a lookup formula

It doesn't move data but moves your viewpoint of the data


It works using a combination of Origin location, Row and Column Offsets and Row and Column Ranges

The format is =Offset(Reference, Rows, Cols, Height, Width)

The Height and Width are optional and default to 1


Examples

=Offset(A1,10,0) will return the value is A11


=Offset(A1,0,10) will return the value is K1


=sum(Offset(A1,10,0,5,1)) will return the sum of values in A11:A15


=sum(Offset(A1,0,10,5,1)) will return the sum of values in K1:K5

=sum(Offset(A1,0,10,1,5)) will return the sum of values in K1:O1


=sum(Offset(A1,0,10,5,5)) will return the sum of values in K1:O5


It is used in Dashboards as you can have a dashboard setup where you enter 1 piece of data (Typically a date) and the dashboard will lookup the appropriate data from a data table and reformat it to show what ever is in the dashboard


Have a look at:


http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

 

[montycarlo]

thanks for giving the detailed understanding on offset.