Power Functions : The Mighty Offset.
Generally when we use Excel Functions, they return a single value by operating on a range of cells. For example ;
would add up the contents of cells A1 to A10, and show the result in the cell where the formula was placed.
The offset function can return a single value, or a range of values which you can subsequently feed into other functions.
An example of the use, and the format of the offset function is as shown below ;
In the above example, I've used the function offset as the input to a
SUM function. In my data table (cells B2 to D14) I've got sales of
sunglasses and scarfs by month. I want to be able to compare sales
across multiple months, allowing a user to select start and end months
and show the result. In cells C18 and C19 I enter the start and end
months and name the cells start and end. I also name cells C3 to C14
"Sunglasses_Sales", and D3 to D14 "Scarfs Sales".
To set up the sum function so that it uses the start and end months
I've entered, I use the OFFSET function. This allows me to select cells
of a different size and position (ie offset) from an original range. So
if I want to sum months 3 to 9 in range "Scarfs_Sales", I want to work
on a range that is 3 rows down in the range, by 9-3 rows deep.
Using the offset function, I specify ;
- That I'm offsetting against the "Scarfs_Sales" range.
- The next part of the
function is to say how many rows down I want to move. I want to move
down 2 rows (since the start is row 1, so it's row 3-1), so I specify
- I then need to say how many columns across I'm moving, which of course in this case, is zero.
- Now I specify the depth
(or height) of cells. This is a total of 7 months (inclusive of all
months) so it is end - start +1 (9-3+1) = 7.
- Finally I specify the width of the range to return. This is 1 column wide, so I specify 1.