Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Fun with Excel functions 1: OFFSET. By Simon Hurst

by
6th Feb 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

It’s always interesting running training courses. Not only do I often learn different ways of doing things and come across new ways of using the programs I train on, but I also discover how much time and effort even relatively simple features and techniques can sometimes save. This is particularly true of Excel functions. I frequently see some very ingenious, but complicated ways of achieving a particular aim in a spreadsheet which, if the creator had been aware of the existence of a particular function, could have been achieved in fraction of the time, and often with less likelihood of error.

Of course, we’re now in ‘you never know what you don’t know’ territory. If you don’t know that a particular feature exists you may not even think of looking for it, and even if you do, it may not be that easy to find.

A good example occurred on a recent training course, concerning the OFFSET() function. This is one of Excel’s Lookup functions and enables a formula to refer to a cell a given number of rows and/or columns from the starting location. So =OFFSET(A1,2,2) would return the contents of the cell two columns to the right and two rows below A1 - C3. Typing in the row and column offsets as numbers rather defeats the point as you could just change the original starting reference. The real use of the function is to use a value in another cell to set the offset.

For example, if you have 12 columns of data representing the 12 months of the year, you may want to construct a report that looks at a particular month. By typing a number into a cell, say into A2, you could then refer your OFFSET() formula to A2, so that it returned the value in the relevant month: =OFFSET(A10,0,A2-1). In this example, the formula would return the value in row 10, for whichever column we type the number of in cell A2.

We have used the expression ‘A2-1’ so we can start the counting from 1 rather than the slightly less intuitive zero. So if we entered 3, for the third month in A2, our reference to A10 would be offset by 3-1 = 2 columns, so would refer to C10 where indeed the figures for our third month sit. This is a great deal more straightforward than, for example, using 12 IF() function statements to work out which cell reference to use.

Another Excel function can extend this idea a little. We could use the MONTH() function to extract the month number from a date, and refer our OFFSET() formula to the cell that contains the MONTH() function.

You might also find it worth your while to bring up the Paste Function or Insert Function screen and look through it category by category to see what’s there. You may find the answer to some long-standing question, or in future realise that the answer to a new question may lie in an Excel function.

The Fun with Excel functions series

2. ABS, SUMIF and the array formulae
3. Expenses tracking tutorial for Excel 2007
4. New Excel 2007 IFERROR function

Also see ExcelZone Compendium: Functions & Formulae

Subscribe to the ExcelZone newswire
To keep up with spreadsheet issues and regular tutorials, click the button below to subscribe to the free monthly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB.co.uk home page after it adds your name to the subscription list.

Tags:

Replies (5)

Please login or register to join the discussion.

avatar
By User deleted
06th Feb 2007 10:20

Very dangerous ......
Relative positions are very dangerous - especially in the light of the debate about Auditable Excel spreadsheets

The concept is more easily justified in a controlled environment of structured 'traditional programming'. However, a disaster scenario is far more likely where users can provide 'ad hoc' amendments without necessarily understanding the impact of adding or removing row/column; also very few spreadsheets are documented in any way which makes the whole thing more challenging

Finally auditing relative (offset) references is a very difficult task

Thanks (0)
Simon Hurst
By Simon Hurst
06th Feb 2007 14:23

Depends how it's used
Thanks JC for your very useful comments. Like many things I think it depends on how the function is used. In a spreadsheet with no documentation, and allowing for uncontrolled ad-hoc user changes, almost any function or formula in Excel is pretty dangerous, but the point you make is an important one, spreadsheets should always be designed with regard for reliability and auditability.

Regards

Simon

Thanks (0)
avatar
By listerramjet
06th Feb 2007 22:20

i am an anorak and proud of it!
offset is indeed most useful, but I have sometimes found it more useful in combination with indirect. If you use offset in the manner described, but rather than values it returns a cell reference from a lookuptable, then you can use indirect to get the data in that cell.

I have seen this one used in several different ways - and it still amuses me.

JC makes an interesting point about the danger of spreadsheets, even if it sounds somewhat luddite. BUT the point about stuff like offset and indirect is that it can be used to make a spreadsheet much easier to audit/debug. Excel contains so much functionality - easier to keep it simple if you know what is available! So I am looking forward to more of this from Simon.

Thanks (0)
Simon Hurst
By Simon Hurst
23rd Feb 2007 11:14

Offset and external workbook
It should work if the other workbook is open I think. If you do work with linked workbooks it is usually safer to make sure they are all open anyway, as Excel has a better chance of updating any reference changes arising from the structure of one of the workbooks being changed. Using range names also helps Excel to keep track of linked cells and areas. You may find Excel's File, Save Workspace option useful when working with mutliple linked workbooks.

If you needed the Offset to work with the linked workbook closed, then you could try linking a sheet in the workbook containing the offset function to the relevant area of the other workbook, and changing the offset function to refer to the linked area, rather than the other workbook directly.

As I'm sure you're aware, you do need to take great care when linking between different workbooks and build in appropriate checks and controls.

Hope this helps

Regards

Simon

Thanks (0)
avatar
By aainslie
23rd Feb 2007 08:37

across workbooks
I find the offset function invaluable as it saves a lot of time when updating YTD values but I find that it only works within the same workbook. When I try to use it linking to another workbook I get a VALUE# error. Any ideas whether there is a way to link to another workbook?

Thanks (0)