Excel Hints & Tips – The OFFSET Function

Excel Courses in the London AreaHow do I set my spread-sheet to automatically extract this month’s figure from the accounts?

 

Let us assume the spread-sheet uses the standard accounts layout of figures for each month, so 12 columns of figures in all, for January to December. You want to indicate the current month with a number (1 for January, 2 for February etc) and have this month’s figure automatically displayed separately.

One approach is to use a very complicated IF statement:

IF(P1=1,C4,if(P1=2,D4,if(P1=3,E4 … and so on up to P1=12

However this is long-winded and complex and therefore invites errors. Our rule of thumb is if you get to three levels of Ifs, there is probably a simpler way to do it. And in this case there is a much simpler way.

The OFFSET Function

OFFSET is a neat little function which allows you to say, from any cell go 3 to the right and 4 down – or, better, use cell references. So to extract this month’s accounts:

=OFFSET(B4,0,P$1)

P1 is the number of the month. B4 is the column before the financial data starts, and the 0 indicates you don’t want to move down a row. So it will take the value of C4 when when P1 is 1, D4 when P1 is 2 and so on.

Excel Offset

If you have any Excel questions or hints & tips you would like shared get in touch!

If you would like any further information on the Excel courses we train, go to our website

Remember – If you’ve been on a Happy Computers course you get 2 years free helpline access.  Call the helpline on 0207 375 7373.

Happy Computers

Leave a Reply

Your email address will not be published. Required fields are marked *