Depreciation - Nested IF Function?

[slstewart249]

I am trying to write a formula to calculate Depreciation. Each row has a single Capex Item with the columns showing Q1, Q2, Q3, Q4 & # of Years Useful Life. For this formula I've assumed the month has 5 weeks (cell AD11).

FIRST ROW WITH DEPRECIATION FORMULA
=IF($F15>0,($F15/($M15)/52)*AD$11,IF($G15>0,($G15/($M15)/52)*AD$11,IF($H15>0,($H15/($M15)/52)*AD$11,IF($I15>0,($I15/($M15)/52)*AD$11," "))))

F15 is $100K for Item 1, bought in Q1. M15 is # Years useful life which is "5." 52 Weeks in year and AD11 is "5" weeks in month. Result is $1.9K deprecation/month in Q1

SECOND ROW WITH DEPRECIATION FORMULA
=IF($F16>0,($F16/($M16)/52)*AD$11,IF($G16>0,($G16/($M16)/52)*AD$11,IF($H16>0,($H16/($M16)/52)*AD$11,IF($I16>0,($I16/($M16)/52)*AD$11," "))))

F15 is $0, G15 is $100K for Item2 bought in Q2. Using this formula, the result is ALSO $1.9K depreciation/month in Q1.

I want the Depreciation result to be 0 if no Capex was bought in a Quarter.

What is wrong with this formula? Please help!

 

[Chihiro]

Few questions.
1. What months falls in which quarter?
2. What is the expected result?
3. Normally I do depreciation calculation based on monthly basis with consistent depreciation value/rate. You have different amount for every 3 month. Is this intended?

 

[slstewart249]

Hi
Q1 is Jul/Aug/Sep, Q2 is Oct/Nov/Dec etc. I am assuming depreciation starts in the SECOND Month of each quarter.

The expected result is if $100K Capex bought in Q1 depreciation starts in August at $1.9K/month continuing for rest of year. If a $100K Capex item bought in Q2 depreciation should start in November at $1.9K/M and continue for the rest of year.

With the current formula, depreciation starts in August at $1.9K/M regardless if the item is bought in Q1 or Q2. I need to edit the formula so that if Q1 (Col F) is zero Capex purchases, then Jul/Aug/Sept reflects zero depreciation.
Thanks!

 

[Chihiro]

Add helper column with following formula to indicate starting month (Col N).
=CHOOSE(MATCH(1,$F15:$I15,-1),2,5,8,11)

Then nest your formula in.
=IF($N15>Q$10,"","YourFormula")

Or combine it into one...
=IF(CHOOSE(MATCH(1,$F15:$I15,-1),2,5,8,11)>Q$10,"","YourFormula")

See attached.

 

[slstewart249]

That's great. Thank you! Now what if I have spend in Q1 and Q3 on a single row? How can the formula be adjusted to calculate Q1 Depreciation starting in August at $1.9K to January, then start to be $4K in Feb? I attached a file. I really appreciate the help! I've been pulling my hair out trying to get this formula to work!

 

[Chihiro]

Here you go. Since Match() can return only one value, changed formula structure a bit.

=IF($F15="",0,IF(O$10>=2,($F15/($M15)/52)*O$11,0))+IF($G15="",0,IF(O$10>=5,($G15/($M15)/52)*O$11,0))+IF($H15="",0,IF(O$10>=8,($H15/($M15)/52)*O$11,0))+IF($I15="",0,IF(O$10>=11,($I15/($M15)/52)*O$11,0))

Note that it will now show zero instead of blanks.

 

[slstewart249]

Awesome! Thank you!!!! You saved me HOURS & HOURS!