Depreciation formula using OFFSET function

[dportillo]

I am trying to use the OFFSET function to model depreciation, it works great when there is no blank columns. I need to insert columns in order to align all my templates and have totals by year and quarter. When I insert columns I cant find a way to exclude the blank columns and pick up from the last column with real data. Attached is an example of the formula and worksheet.


=-SUM(OFFSET(F12,0,- MIN(COUNT($C$12:F12), $B$4)+1,1, MIN(COUNT($C$12:F12), $B$4)))/$B$4

Thanks in advance!

[hoyasaxa215]

I would first insert all the columns for the quarters in every year of data that you're doing this for. Then you can go in and change the two COUNT cell references. Instead of counting a range of cells ($K$12:P12), you can just manually enter the specific cells that contain the annual data (K12,L12,P12). Then you can copy and paste that equation into the other years and it will automatically update the references to the appropriate cells in that table.

[dportillo]

Hi hoyasaxa215 - I added the columns to the new file, not sure I follow. I tried replacing the min(count with the cells manually and it returned an error. Thanks!