Lunchtime learning - Excel |
|
|||||
Return
to lunchtime menu
Do make sure that any tips and suggestions work as you expect them to in your own particular circumstances. Search other Excel resources (AccountingWeb, Beancounters Guide, IT Counts): Loading
Johnny Depp explains Excel OFFSET()IntroductionOFFSET() is a very useful Excel function. It can allow you to choose a cell, or a block of cells, based on the contents of another cell. OFFSET() has two forms. The simpler form has three arguments:
This allows you to return the value of a cell a given number of rows and columns 'offset' from the starting cell. So OFFSET(B4,1,1) would move one row down and one column right from the starting position of B4 to return the value in cell C5. Negative numbers move up and left and the numbers of rows and columns can come from references to other cells. The more complicated form has five arguments and allows you to specify a rectangle based on a starting cell. This can be useful in creating dynamic ranges for aggregate functions such as SUM(). The five arguments are as follows:
For example, this formula: =SUM(OFFSET(E8,0,1,2,3)) Would sum a rectangle starting at: E8 plus zero rows, plus one column; so F8 You might have noticed that OFFSET() bears a striking similarity to pirate treasure map instructions, so here's an attempt to explain the function using a combination of Excel form controls and conditional formatting:
|
|
|||||
© 1998 The Knowledge Base. Disclaimer Contact info@tkb.co.uk Design by Reading Room Ltd 1998 |