Add-Innovation Home

About Add-Innovation

Contact Us

 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 ;

=Sum(A1:A10)

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 ;

example of offset

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 ;

  1. That I'm offsetting against the "Scarfs_Sales" range. 
  2. 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 start-1.
  3. I then need to say how many columns across I'm moving, which of course in this case, is zero.
  4. 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.
  5. Finally I specify the width of the range to return. This is 1 column wide, so I specify 1.