I have the following sales data for 2016, 2017, and 2018:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]
[TD]Column X[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]Opportunity Name
[/TD]
[TD]Opportunity Close Date (US date)
[/TD]
[TD]Opportunity Subscription Months
[/TD]
[TD]Amount
[/TD]
[TD]Opportunity Record[/TD]
[TD]Jan 16[/TD]
[TD]Feb 16[/TD]
[TD]Mar 16[/TD]
[TD]Apr 16[/TD]
[TD]All months all the way until Dec 2018[/TD]
[/TR]
[TR]
[TD]
[TD]Company 1
[/TD]
[TD]10/11/16
[/TD]
[TD]12
[/TD]
[TD]$13000
[/TD]
[TD]Subscription
[/TD]
[TD]?
[/TD]
[TD]
[/TD]
[TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Company 2
[/TD]
[TD]11/5/17
[/TD]
[TD]12
[/TD]
[TD]$12300
[/TD]
[TD]
[/TD]
[TD]
[TD]
[/TD]
[TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Company 2
[/TD]
[TD]1/1/18
[/TD]
[TD]4
[/TD]
[TD]$8900
[/TD]
[TD]<strike></strike>
[/TD]
[TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Company 3
[/TD]
[TD]2/5/16
[/TD]
[TD]2
[/TD]
[TD]$2000
[/TD]
[TD]
[/TD]
[TD]
[TD]
[/TD]
[TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]Company 3
[/TD]
[TD]10/3/17
[/TD]
[TD]7
[/TD]
[TD]$3051
[/TD]
[TD]
[/TD]
[TD]
[TD]
[/TD]
[TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TD]Company 5
[/TD]
[TD]12/1/16
[/TD]
[TD]6
[/TD]
[TD]$5605
[/TD]
[TD]Pilot
[/TD]
[TD]
[/TD]
[TD]
[TD]
[/TD]
[TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Based on this information, is there some formula or strategy here that I can use to break out how much money each of the companies would pay us by month all the way until Dec 2018? Specifically, is there a simple formula or set of formulas I can use to copy across and down (I have several hundred rows). For example, in column F row 2, it would be $0 since this contract doesn't start until 10/11/16. However, in column G row 5, it would be $1000 ($2000/2 = $1000 p/month). Column H row 5 would also be $1000. Then Column I row 5 would $0.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]
Column B
<strike></strike>[/TD][TD]
Column C
[/TD][TD]
Column D
<strike></strike>[/TD][TD]
Column
E[/TD][TD]
Column F
<strike></strike>[/TD][TD]
Column G
[/TD][TD]
Column H
<strike></strike>[/TD][TD]
Column I
[/TD][TD]Column X[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]Opportunity Name
[/TD]
[TD]Opportunity Close Date (US date)
[/TD]
[TD]Opportunity Subscription Months
[/TD]
[TD]Amount
[/TD]
[TD]Opportunity Record[/TD]
[TD]Jan 16[/TD]
[TD]Feb 16[/TD]
[TD]Mar 16[/TD]
[TD]Apr 16[/TD]
[TD]All months all the way until Dec 2018[/TD]
[/TR]
[TR]
[TD]
Row 2
<strike></strike>[/TD][TD]Company 1
[/TD]
[TD]10/11/16
[/TD]
[TD]12
[/TD]
[TD]$13000
[/TD]
[TD]Subscription
[/TD]
[TD]?
[/TD]
[TD]
?
<strike></strike>[/TD]
[TD]
?
[/TD][TD]
?
<strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Row 3
<strike></strike>[/TD]
[TD]Company 2
[/TD]
[TD]11/5/17
[/TD]
[TD]12
[/TD]
[TD]$12300
[/TD]
[TD]
Subscription
<strike></strike>[/TD]
[TD]
?
[/TD][TD]
?
<strike></strike>[/TD]
[TD]
?
[/TD][TD]
?
<strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Row 4
<strike></strike>[/TD]
[TD]Company 2
[/TD]
[TD]1/1/18
[/TD]
[TD]4
[/TD]
[TD]$8900
[/TD]
[TD]
Pilot
[/TD]
[TD]
?
[/TD][TD]
?
<strike></strike>[/TD]
[TD]
?
<strike></strike>[/TD]
[TD]
?
<strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Row 5
<strike></strike>[/TD]
[TD]Company 3
[/TD]
[TD]2/5/16
[/TD]
[TD]2
[/TD]
[TD]$2000
[/TD]
[TD]
Pilot
<strike></strike>[/TD]
[TD]
?
[/TD][TD]
?
<strike></strike>[/TD]
[TD]
?
[/TD][TD]
?
<strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Row 6
<strike></strike>[/TD]
[TD]Company 3
[/TD]
[TD]10/3/17
[/TD]
[TD]7
[/TD]
[TD]$3051
[/TD]
[TD]
Subscription
<strike></strike>[/TD]
[TD]
?
[/TD][TD]
?
<strike></strike>[/TD]
[TD]
?
[/TD][TD]
?
<strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Row
7[/TD][TD]Company 5
[/TD]
[TD]12/1/16
[/TD]
[TD]6
[/TD]
[TD]$5605
[/TD]
[TD]Pilot
[/TD]
[TD]
?
<strike></strike>[/TD]
[TD]
?
[/TD][TD]
?
<strike></strike>[/TD]
[TD]
?
[/TD][TD][/TD]
[/TR]
</tbody>[/TABLE]
Based on this information, is there some formula or strategy here that I can use to break out how much money each of the companies would pay us by month all the way until Dec 2018? Specifically, is there a simple formula or set of formulas I can use to copy across and down (I have several hundred rows). For example, in column F row 2, it would be $0 since this contract doesn't start until 10/11/16. However, in column G row 5, it would be $1000 ($2000/2 = $1000 p/month). Column H row 5 would also be $1000. Then Column I row 5 would $0.
Thoughts? Advice? Please help! Thanks!