How to use the offset function (Part II)

Offset function can be used to sum up a range. So what if it can sum up ranges? It means that you could do wonderful things with this. For example, with the offset function, I could reduce the calculation of depreciation to a single line. Without it, you would need to a worksheet just to calculate depreciation. First let me give you the basic introduction, continue from yesterday.

 

Enter this formula "=SUM(OFFSET(A1,0,0,4,4))" say in row F2. It will show the results as 9. Excel will sum up the numbers appearing in A1 to D4 since we specific in the offset function that it should add up from cell A1 (Offset(A1,0,0,4,4) and 4 columns to the right (A1,0,0,4,4) and 4 rows down (A1,0,0,4,4). If you were to change the formula to 3 rows down, like this, (A1,0,0,3,4), you will get the results 5.

 

	A	B	C	D
1	5
2
3
4				4

 

Now that you have learnt how to sum up the range using offset, here is the application. When you calculate depreciation, you need to calculate the residual value of your fixed asset. In this case you can apply the offset function to the row on Depreciation. THe formula is "=SUM(OFFSET(D11,0,0,1,-4))/4". The offset function will sum up the net asset value of the last 4 years in row 11.

 

9	C	D	E	F	G
10		Year 1	Year 2	Year 3	Year 4
11	Asset Value	10,000
12	Depreciation	2,500	2,500	2,500	2,500
13	Accumulated Depreciation	2,500	5,000	7,500	10,000

 

Some of you must be wondering, I could be using relative cells and it will sum up the value in the last 4 cells. Same as what offset is doing. You are right if yo have thought of that. Then consider this. I will now change the formula to reference the number of years of depreciation to reference to a cell (say J9). The formulat would have changed to "=SUM(OFFSET(D11,0,0,1,-J9))/4". With this, I could change the deprecaition year easily at Cell J9, Try doing this with the relative cells range you have suggested. Are you now convinced?