need help for a simpler deferred income formula

[mdubdroid]

spreadsheet is about 15-20k lines usually would like something a bit simpler and maybe more dynamic rather than having to manually adjust for which type of invoice is being deferred in the current month.
type 1 starts in current month, type 2 starts two months out from date.

example scenario:
[TABLE="width: 500"]
<tbody>[TR]
[TD]region[/TD]
[TD]pd[/TD]
[TD]type[/TD]
[TD]type[/TD]
[TD]date[/TD]
[TD]start date[/TD]
[TD]end date[/TD]
[TD]name[/TD]
[TD]amount[/TD]
[TD]# days[/TD]
[TD]daily amt[/TD]
[TD]11/30/15[/TD]
[TD]12/31/15[/TD]
[TD]1/31/16[/TD]
[TD]2/29/16[/TD]
[TD]3/31/16[/TD]
[/TR]
[TR]
[TD]north[/TD]
[TD]2015-11[/TD]
[TD]inv[/TD]
[TD]2[/TD]
[TD]11/15/15[/TD]
[TD]01/01/16[/TD]
[TD]12/31/16[/TD]
[TD]client 1[/TD]
[TD]10000[/TD]
[TD]365[/TD]
[TD]27.40[/TD]
[TD]10000[/TD]
[TD]10000[/TD]
[TD]9178[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]south[/TD]
[TD]2015-11[/TD]
[TD]inv[/TD]
[TD]1[/TD]
[TD]11/15/15[/TD]
[TD]11/1/15[/TD]
[TD]10/31/16[/TD]
[TD]client 2[/TD]
[TD]5000[/TD]
[TD]365[/TD]
[TD]13.70[/TD]
[TD]4603[/TD]
[TD]4178[/TD]
[TD]3753[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]south[/TD]
[TD]2015-11[/TD]
[TD]inv[/TD]
[TD]1[/TD]
[TD]11/15/15[/TD]
[TD]11/1/15[/TD]
[TD]10/31/16[/TD]
[TD]client 3[/TD]
[TD]1000[/TD]
[TD]365[/TD]
[TD]2.74[/TD]
[TD]921[/TD]
[TD]836[/TD]
[TD]751[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]


the current formula is:
=IF($L3>=0,IF(IF($F3>P$2,0,$L3-((P$2-$F3)*$N3))<0,0,IF($F3>P$2,0,$L3-((P$2-$F3)*$N3))),IF(IF($F3>P$2,0,$L3-((P$2-$F3)*$N3))>0,0,IF($F3>P$2,0,$L3-((P$2-$F3)*$N3))))

when i initially put new items in the sheet, i still have to manually adjust the formula to equal the amount for the type 2 invoices for the 1st two months, and then copy out the regular formula for the next 12.
tried some searches but couldnt quite find something that has worked.

thanks in advance!

 

[DRSteele]

Please do us a favour and post your query again using the HTML maker (which you can find in my signature below) because it is difficult to ascertain which data goes in which columns. Also, in the future please be considerate of the readers and compose your posts using proper punctuation and proper upper-case and lower-case spelling.

 

[mdubdroid]

Much neater, thanks. Basically, having trouble adding an extra IF statement to the beginning of the formula; to say "if its a renewal, delay 2 months, if not amortize immediately"

Excel 2010

	D	E	F	G	H	I	J	K	L	M	N	O	P
CLIENT 1
CLIENT 2
CLIENT 3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="bgcolor: #4BACC6, align: center"]PD[/TD]
[TD="bgcolor: #4BACC6, align: center"]TYPE[/TD]
[TD="bgcolor: #4BACC6, align: center"]CAT[/TD]
[TD="bgcolor: #C00000, align: center"]DATE[/TD]
[TD="bgcolor: #C00000, align: center"]DEF STAT[/TD]
[TD="bgcolor: #C00000, align: center"]DEF END[/TD]
[TD="bgcolor: #C00000, align: center"]NAME[/TD]
[TD="bgcolor: #C00000, align: center"] AMT [/TD]
[TD="bgcolor: #4BACC6, align: center"]# DAYS[/TD]
[TD="bgcolor: #4BACC6, align: center"] DAILY AMT [/TD]
[TD="bgcolor: #4BACC6, align: center"]11/30/2015[/TD]
[TD="bgcolor: #4BACC6, align: center"]12/31/2015[/TD]
[TD="bgcolor: #4BACC6, align: center"]01/31/2016[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]2015-11[/TD]
[TD="align: center"]INV[/TD]
[TD="align: center"]RENEWALS[/TD]
[TD="align: center"]11/15/15[/TD]
[TD="align: center"]01/01/16[/TD]
[TD="align: center"]12/31/16[/TD]

[TD="align: right"] 10,000.00 [/TD]
[TD="align: right"]365[/TD]
[TD="align: right"] 27.40 [/TD]
[TD="align: right"] 10,000 [/TD]
[TD="align: right"] 10,000 [/TD]
[TD="align: right"] 9,178 [/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]2015-12[/TD]
[TD="align: center"]INV[/TD]
[TD="align: center"]NEW BUS[/TD]
[TD="align: center"]11/15/15[/TD]
[TD="align: center"]11/01/15[/TD]
[TD="align: center"]10/31/16[/TD]

[TD="align: right"] 5,000.00 [/TD]
[TD="align: right"]365[/TD]
[TD="align: right"] 13.70 [/TD]
[TD="align: right"] 4,603 [/TD]
[TD="align: right"] 4,178 [/TD]
[TD="align: right"] 3,753 [/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]2015-12[/TD]
[TD="align: center"]INV[/TD]
[TD="align: center"]NEW BUS[/TD]
[TD="align: center"]11/15/15[/TD]
[TD="align: center"]11/01/15[/TD]
[TD="align: center"]10/31/16[/TD]

[TD="align: right"] 1,000.00 [/TD]
[TD="align: right"]365[/TD]
[TD="align: right"] 2.74 [/TD]
[TD="align: right"] 921 [/TD]
[TD="align: right"] 836 [/TD]
[TD="align: right"] 751 [/TD]

</tbody>

Sheet2​

Current formula: CURRENT FORMULA=IF($L3>=0,IF(IF($F3>P$2,0,$L3-((P$2-$F3)*$N3))<0,0,IF($F3>P$2,0,$L3-((P$2-$F3)*$N3))),IF(IF($F3>P$2,0,$L3-((P$2-$F3)*$N3))>0,0,IF($F3>P$2,0,$L3-((P$2-$F3)*$N3))))

Thanks.

 

[DRSteele]

What you've pasted here does not line up with the formula. For example, what is this clause $F3>P$2 supposed to be doing? And what is in P2?

 

[mdubdroid]

What you've pasted here does not line up with the formula. For example, what is this clause $F3>P$2 supposed to be doing? And what is in P2?

As you can tell I'm a newbie here. That is actually the full formula in my spreadsheet, but I condensed the table here so it was easier to read. The items displayed are really the ones that would drive the formula though.