=SUMIF Problem

[doctorjones_md]

I'm working on a Straight Line Depreciation Spreadsheet (this was a "Home
Grown" effort -- touched by many hands, and not derived from an Excel
Template/Example)

Basically, I have the following ...
Cost (CELL M2) -- Life, in months (CELL L2) -- In-Service Date (CELL J2) --
Depreciation per month (CELL N2) -- Life/Cost)

Beginning 01/01/2005, I have an "Accumulated Depreciation Total" (CELL O2)
and the logic I want to apply is this:

IF Value in Cell O2 < Cost (CELL M2) then I want to populate CELL P2 with
the value of CELL P2 + the Monthly Depreciation (CELL N2) and continue this
on out (giveing an Accumulated Depreciation Total 12/31/06 and each
end-of-year until the value in any subsequent cell is equal to the Cost
(CELL M2).

Is there some code that I could use to manage this? For example (based on
the Life -- 60 months, 120 months, ect. -- Auto Fill the appropriate number
of cells to the right to correspond with the number of months in the Life
Cycle.

Thanks in advance for any assistance in this troubling problem.

 

[Roger Govier]

Hi

With your Cost of Asset in M2, Life of Asset in L2, choose which cell
you want to start Depreciation and enter the following formula
=IF($M2="","",IF(COLUMN(A:A)<=$L2,($M2/$L2)*COLUMN(A:A),""))
Copy across the page as far as required and each successive cell will
show the cumulative Depreciation to that month and it will cease and
show blank in the first month which exceeds the life of the asset.

It works as follows
The first test is whether there is any value in column M, if not return
null.
Column() returns the column number, therefore Column(A:A) will return 1.
As you copy across the page it will step up to B:B, C:C etc. and return
2, 3 etc.
If the number returned by the column() is less than or equal to the life
of the asset, then the formula returns the appropriate number of months
worth of depreciation, else it returns null.