Excel Tips & Tricks: Using Offset function for Calculation

Scenario: Given is the monthly data of a firm, we need to find out YTD and make it dynamic so that whichever month is selected by user, YTD of that month should appear. Which means data of all the previous months, including the selected month should be added up.

Solution : To create this we are going to use Data Validations for enabling user to select the month; Match function for finding column number which contains selected month. Sum for adding up the data and more importantly Offset function.

Syntax for Offset: =Offset(reference,row,cols,[height],[width])

Explanation :

Parameter	Description
Reference	Starting cells
Rows	Number of rows to be moved from starting point
Cols	Number of columns to be moved from starting point
[Height]	Optional Parameter; Total number of rows to be included from start point
[Width]	Optional Parameter; Total number of columns to be included from start point

Data Example:

The Data Sheet

Considering the data shown above, Let say we want to calculate Total sales up till the selected month.In cell O1 Data Validation is used so as to enable user to select the month.

Now to find how many months are selected we can use Match function in cell P1. In Match, we can select the O1 cell as Lookup value, the headers in data as the Array and 0 for exact match. And so, the formula will be:

=MATCH(O1,C2:N2,0)

So if June is selected, answer of Match function will be 6, as June is written in the 6th cell of the selected range in Match.

Now to calculate YTD, we can use Offset. In Offset, our starting cell is the first cell of Jan data i.e. C3. This is because that is from where we will start adding. In this case we do not have to move any number of rows or columns. Our starting point is always same thus we can put 0 (Zero) for rows and Cols parameter.

Similarly the height is also Zero as for one sales person, we have to consider data written in one row. Thus, the data does not stretch Row- Wise.

However, the Width is needed as we need to include multiple cells in one row at a time. So if Jun is selected, we would need total of 6 cells (From Jan to Jun) in a single row but multiple columns. This height we have pre-Calculated using Match function in cell P1.

And so our Offset Function Should be : =OFFSET(C3,0,0,1,$P$1)

Now whichever range is selected by Offset needs to be totalled up in order to calculate YTD. Thus, we can combine Offset with Sum function. =SUM(OFFSET(C3,0,0,1,$P$1))