Excel Hints & Tips - The OFFSET Function
How 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:
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.
Hi, we are Happy
We are leading a movement to create happy, empowered and productive workplaces.
How can we help you and your team to find joy in at least 80% of your work?
Why choose Happy?
- Award-winning learner-focused approach
- Small class sizes to give personal one-to-one support
- Two years of free support after your course
- Private group options available
- No quibble money-back guarantee on all courses
Trusted Customer0 day ago