Formula to allocate amounts by month

thp510

Board Regular
Joined
Oct 19, 2015
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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>
 
Last edited:
Upvote 0
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!
 
Upvote 0
Re: Need help with cacluation (simple formula?)!!

Yes I stupidly pasted the formula from row 3 in my worksheet
 
Upvote 0
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
 
Last edited:
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top