Half YR Depr. Forecast Nested IF Statement

[creepinjorge]

Excel 2010

Hello,

I would like to create a formula or VBA code that enters the appropriate depreciation amount for each month. The goal is that an engineer can enter their project closing month and depreciation years and the formula will return the current year depreciation.


Background:Half yr. Convention. Calendar year Jan=1 Dec=12; A project closes in March(3) we catch up Jan-Feb then depreciate the remaining months so an entire half-yr is taken.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl79, width: 90"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9 (AA3)[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl78, width: 77"]Month Closing[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl78, width: 77"]Years[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl78, width: 107"]Half-Year
Depreciation[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl79, width: 90"]31[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl79, width: 90"]30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl79, width: 90"]31[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]31[/TD]
[TD]28[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]365[/TD]
[/TR]
[TR]
[TD]9 (P5)[/TD]
[TD]10[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl77, width: 107"] 186,705.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF($P5=AA$3,$R5-$AD5-$AC5-$AB5,($R5/$AF$4)*AA$4)[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl77, width: 107"] 7,490.00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=IF($P6=S$3,$R6-$AD6-$AC6-$AB6-$AA6-$Z6-$Y6-$X6-$W6-$V6-$U6-$T6,IF($P6=S$3,$R6-$AD6-$AC6-$AB6-$AA6-$Z6-$Y6-$X6-$W6-$V6-$U6,IF($P6=S$3,$R6-$AD6-$AC6-$AB6-$AA6-$Z6-$Y6-$X6-$W6-$V6,IF($P6=S$3,$R6-$AD6-$AC6-$AB6-$AA6-$Z6-$Y6-$X6-$W6,IF($P6=S$3,$R6-$AD6-$AC6-$AB6-$AA6-$Z6-$Y6-$X6,IF($P6=S$3,$R6-$AD6-$AC6-$AB6-$AA6-$Z6-$Y6,IF($P6=S$3,$R6-$AD6-$AC6-$AB6-$AA6-$Z6,IF($P6=S$3,$R6-$AD6-$AC6-$AB6-$AA6,($R6/$AF$4)*S$4))))))))[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD]same[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl77, width: 107"] 328.70[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF($P7=V$3,$R7-$AD7-$AC7-$AB7-$AA7-$Z7-$Y7-$X7-$W7,($R7/$AF$4)*V$4)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl77, width: 107"] 30,074.35[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl77, width: 90"] 5,026.12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl77, width: 90"] 2,554.26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl77, width: 90"] 2,554.26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl77, width: 90"] 2,307.07[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl77, width: 90"] 2,554.26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl77, width: 90"] 2,471.86[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl77, width: 90"] 2,554.26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl77, width: 100"] 2,471.86[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl77, width: 90"] 2,554.26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="class: xl77, width: 90"] 2,554.26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl77, width: 100"] 2,471.86[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD="class: xl79, width: 100"] 30,074.35[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

If the Month closing = current month then subtract everything to right. if no match, take the depreciation that month by days in month. The long nested IF doesn't work because It stops at 8 statements.

I'm not a wiz at VBA so IF Statements that move across months aren't the first thing I think about.

Thank you for your time!

Dennis

 

[RonB1111]

Using your last row of data which I think would begin in P8, enter this formula in S8 and copy across:

=IF($P8>S$3,0,IF($P8=S$3,$R8/12*$P8,$R8/12))

 

[creepinjorge]

That is fantastic! and extreamly useful considering how often I forecast depreciation and how many projects are opened/closed! You made this process painless. Stay classy.

 

[RonB1111]

You're welcome!

 

[creepinjorge]

=IFERROR(IF($P5>S$3,0,IF($P5=S$3,($R5/365)*SUM($S$4:S$4),$R5/365*S$4)),0)

I updated the formula to use days (our depreciation drills down to days in month not just /12) and move across the sheet also. Again, thanks so much for your time and everything.