Formula to allocate amounts by month

[thp510]

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]

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​

​

<strike></strike>
[/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!​

 

[Yongle]

Re: Need help with cacluation (simple formula?)!!

Try

In F2 copy down and across

=IF(AND(F$1 > $B3,F$1 < EDATE($B3,$C3)),ROUND($D3/$C3,0),"")<edate($b2,$c2)),round($d2 $c2,0),"")<="" strong="">

</edate($b2,$c2)),round($d2><edate($b2,$c2)),round($d2 $c2,0),"")<="" strong="">Remove the spaces before and after > and < </edate($b2,$c2)),round($d2><edate($b2,$c2)),round($d2 $c2,0),"")<="" strong="">
(inserted to get around a posting issue!)
</edate($b2,$c2)),round($d2>

 

[thp510]

Re: Need help with cacluation (simple formula?)!!

Try

In F2 copy down and across

=IF(AND(F$1 > $B3,F$1 < EDATE($B3,$C3)),ROUND($D3/$C3,0),"")<edate($b2,$c2)),round($d2 strong="" $c2,0),"")<="">

</edate($b2,$c2)),round($d2><edate($b2,$c2)),round($d2 strong="" $c2,0),"")<="">Remove the spaces before and after > and < </edate($b2,$c2)),round($d2><edate($b2,$c2)),round($d2 strong="" $c2,0),"")<="">
(inserted to get around a posting issue!)
</edate($b2,$c2)),round($d2>

Thanks Yongle. However, it seems like it didn't work for me. Here's a quick snapshot of my excel function ​https://imgur.com/3bn8gyT. Also, is there a reason you suggested that I look at the next row of data in the formula? For example, why would F2 pull from data in the 3rd row in the example function you provided? Thanks again for the help!

 

[Yongle]

Re: Need help with cacluation (simple formula?)!!

Yes I stupidly pasted the formula from row 3 in my worksheet

 

[Yongle]

Re: Need help with cacluation (simple formula?)!!

Let's give you the whole solution

The formula for F2 is
=IF(AND(F$1 > $B2,F$1 < EDATE($B2,$C2)),ROUND($D2/$C2,0),"")

To get it working (I should have told you this previously)
- the values in F1, G1 etc must be input as dates
- enter the date in F1 as 1 Jan 2016
- formula in G1 (and copied across)
=EDATE(F1,1)
- format F1 etc with custom format mmm-yy

 

[thp510]

Re: Need help with cacluation (simple formula?)!!

Let's give you the whole solution

The formula for F2 is
=IF(AND(F$1 > $B2,F$1 < EDATE($B2,$C2)),ROUND($D2/$C2,0),"")

To get it working (I should have told you this previously)
- the values in F1, G1 etc must be input as dates
- enter the date in F1 as 1 Jan 2016
- formula in G1 (and copied across)
=EDATE(F1,1)
- format F1 etc with custom format mmm-yy

Amazing! Thank you so much!!! The power of this msg board