Trouble recognizing varying revenue on a monthly basis - formula wrong.

scott_bassman

New Member
<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--> I have included below a spreadsheet. The formulas are not correct, and I have no idea how to correct them. We have a, "start date" for opportunities - this could be any point within a given month. So, if it's a three month project and it starts on 4/15/2013 we would recognize ~half a month of revenue for that April, a full month for May, June, and ~half a month for July, making a total of 3 months. The formulas that are there do not account for this, and say for, "Line 8" they don't do what I need in the above. The different amounts do add up to the correct, "\$500,000," but it's not calculating the correct amounts monthly. Can someone PLEASE help me figure this out? I would like to get the formula right for say, line 8, and apply that formula to the rest of the spreadsheet (if possible). Thanks! Also, I'm using MS Excel 2013, and Windows 8.

Excel 2012
ABCDEFGHIJKLMNOPQR
1 90 108,382 161,273 290,960 323,454 173,281 12,738 - - - -
2 75 168,065 226,896 241,513 171,093 127,545 67,437 - - - -
3 50 43,548 938,602 513,364 218,192 149,087 117,316 - - - -
4Total 319,995 1,588,815 1,704,288 1,661,016 1,129,683 813,339 350,373 264,204 201,300 -
5Created DateOpportunity NameProbability (%)Close DateStart DateProject Duration Amount45678910111213
63/14/2013903/22/20134/1/201316500 6,500 - - - - - - - - -
73/17/2013903/29/20134/19/2013115000 6,290 8,710 - - - - - - - -
82/15/2013903/29/20134/15/20133500000 91,398 136,201 136,201 136,201 - - - - - -
92/28/2013904/17/20135/15/2013238500 - 10,556 13,972 13,972 - - - - - -
102/27/2013903/29/20134/19/2013110000 4,194 5,806 - - - - - - - -
112/18/2013905/6/20136/24/2013266300 - - 8,555 28,873 28,873 - - - - -
122/28/2013905/31/20136/10/20132345000 - - 122,419 111,290 111,290 - - - - -
132/18/2013905/31/20136/24/2013229250 - - 3,774 12,738 12,738 12,738 - - - -
142/18/2013904/30/20136/24/2013246800 - - 6,039 20,381 20,381 - - - - -
152/21/2013753/29/20134/22/2013150000 16,129 33,871 - - - - - - - -
163/14/2013754/5/20134/8/20131150000 116,129 33,871 - - - - - - - -
172/18/2013754/26/20135/10/20133265000 - 20,896 67,437 67,437 67,437 67,437 - - - -
182/28/2013754/26/20135/17/20133215000 - 34,677 60,108 60,108 60,108 - - - - -
193/21/2013756/3/20136/3/2013150000 - - 46,774 3,226 - - - - - -
2011/28/2012755/15/20136/26/2013150000 - - 9,677 40,323 - - - - - -
211/2/2013754/30/20135/6/2013110000 - 8,387 1,613 - - - - - - -
221/2/2013754/30/20135/6/201317000 - 5,871 1,129 - - - - - - -
231/2/2013754/30/20135/6/2013130000 - 25,161 4,839 - - - - - - -
241/2/2013754/30/20135/6/2013121000 - 17,613 3,387 - - - - - - -
253/27/2013754/5/20134/22/20132111000 35,806 46,548 46,548 - - - - - - -
263/25/2013504/15/20134/22/2013135000 11,290 23,710 - - - - - - - -
272/21/2013504/30/20135/1/20134500000 - 125,000 93,750 93,750 93,750 93,750 - - - -
282/28/2013504/30/20135/10/2013365000 - 15,376 16,541 16,541 16,541 - - - - -
293/9/2013506/3/20137/8/2013276900 - - - 29,768 23,566 23,566 - - - -
303/11/2013505/20/20135/20/20131100000 - 38,710 61,290 - - - - - - -
312/27/2013505/17/20135/1/20132200000 - 100,000 50,000 50,000 - - - - - -
324/1/2013505/15/20136/3/2013224500 - - 11,460 6,520 6,520 - - - - -
333/6/2013505/6/20134/30/20131500000 32,258 467,742 - - - - - - - -
343/14/2013505/1/20135/13/20131125000 - 76,613 48,387 - - - - - - -
352/21/2013504/30/20135/17/20131150000 - 72,581 77,419 - - - - - - -
362/18/2013504/30/20136/5/20131100000 - - 87,097 12,903 - - - - - -
373/14/2013504/22/20135/6/2013230000 - 12,581 8,710 8,710 8,710 - - - - -
383/19/2013504/23/20135/29/2013165000 - 6,290 58,710 - - - - - - -
393/9/2013254/26/20135/29/2013250000 - 2,419 23,790 23,790 - - - - - -
402/28/2013254/26/20135/10/20133215000 - 50,860 54,713 54,713 54,713 54,713 - - - -
412/18/2013254/26/20135/10/2013240000 - 14,194 12,903 12,903 - - - - - -
422/28/2013254/26/20135/17/20133215000 - 34,677 60,108 60,108 60,108 - - - - -
433/13/2013257/18/20138/26/20134265000 - - - - 12,823 63,044 63,044 63,044 63,044 -
443/14/2013257/17/20138/5/2013165000 - - - - 56,613 8,387 - - - -
453/14/2013257/1/20137/31/20131100000 - - - 3,226 96,774 - - - - -
463/11/2013256/28/20137/12/2013225000 - - - 8,065 8,468 8,468 - - - -
473/14/2013256/26/20137/1/20131100000 - - - 100,000 - - - - - -
483/21/2013256/21/20137/19/2013235000 - - - 7,339 13,831 13,831 - - - -
493/14/2013256/19/20137/1/20131150000 - - - 150,000 - - - - - -
503/21/2013256/18/20136/28/20134265000 - - 8,548 64,113 64,113 64,113 64,113 - - -
512/18/2013256/14/20137/29/2013150000 - - - 4,839 45,161 - - - - -
523/14/20132510/1/201310/14/20131150000 - - - - - - 87,097 62,903 - -
533/21/2013259/30/201310/11/2013235000 - - - - - - 11,855 11,573 11,573 -
543/11/2013259/27/201310/18/2013225000 - - - - - - 5,645 9,677 9,677 -
553/14/2013258/21/20138/30/20131150000 - - - - 9,677 140,323 - - - -
563/14/2013258/5/20139/2/2013150000 - - - - - 48,387 1,613 - - -
573/21/2013257/19/20138/9/20134265000 - - - - 49,153 53,962 53,962 53,962 53,962 -
583/13/2013257/18/20138/26/20134265000 - - - - 12,823 63,044 63,044 63,044 63,044 -
593/14/2013256/10/20137/1/2013130000 - - - 30,000 - - - - - -
603/23/2013256/10/20136/24/2013160000 - - 15,484 44,516 - - - - - -
613/13/2013256/3/20137/17/2013285000 - - - 20,565 32,218 32,218 - - - -
623/21/2013255/30/20136/28/2013240000 - - 2,581 18,710 18,710 - - - - -
633/11/2013255/30/20136/10/20131200000 - - 141,935 58,065 - - - - - -
643/11/2013255/30/20136/14/2013230000 - - 8,710 10,645 10,645 10,645 - - - -
653/11/2013255/30/20136/14/2013240000 - - 11,613 14,194 14,194 - - - - -
663/21/2013255/24/20136/14/2013250000 - - 14,516 17,742 17,742 - - - - -
673/11/2013255/24/20136/14/2013225000 - - 7,258 8,871 8,871 - - - - -
683/11/2013255/23/20136/24/2013275000 - - 9,677 32,661 32,661 - - - - -
693/14/2013255/15/20135/30/20131100000 - 6,452 93,548 - - - - - - -
7011/28/2012255/15/20136/24/2013120000 - - 5,161 14,839 - - - - - -
7111/28/2012255/15/20136/24/2013150000 - - 12,903 37,097 - - - - - -
723/23/2013255/13/20135/20/2013140000 - 15,484 24,516 - - - - - - -
733/13/2013255/3/20136/17/2013215195 - - 3,676 5,759 5,759 - - - - -
743/11/2013255/1/20135/3/2013120000 - 18,710 1,290 - - - - - - -
752/18/2013254/30/20135/10/2013250000 - 17,742 16,129 16,129 - - - - - -
763/11/2013254/30/20135/17/2013235000 - 8,468 13,266 13,266 - - - - - -
773/11/2013254/30/20135/17/2013240000 - 9,677 15,161 15,161 - - - - - -
783/11/2013254/30/20135/21/2013235000 - 6,210 14,395 14,395 - - - - - -
793/11/2013254/30/20135/17/2013250000 - 12,097 18,952 18,952 - - - - - -
802/28/2013254/26/20135/10/20133215000 - 50,860 54,713 54,713 54,713 54,713 - - - -
813/21/2013254/29/20135/10/2013240000 - 14,194 12,903 12,903 - - - - - -
82 - - - - - - - - - -
83 - - - - - - - - - -
84 - - - - - - - - - -
85 - - - - - - - - - -
86 - - - - - - - - - -
87 - - - - - - - - - -
88 - - - - - - - - - -
89 - - - - - - - - - -
90 - - - - - - - - - -
91 - - - - - - - - - -
92 - - - - - - - - - -
93 - - - - - - - - - -
94 - - - - - - - - - -
95 - - - - - - - - - -
96 - - - - - - - - - -
97 - - - - - - - - - -
98 - - - - - - - - - -
99 - - - - - - - - - -

<tbody>
</tbody>
report1364218650080

Worksheet Formulas
CellFormula
I1=SUMIF((\$C6:\$C126),\$H1,I6:I126)
J1=SUMIF((\$C6:\$C126),\$H1,J6:J126)
K1=SUMIF((\$C6:\$C126),\$H1,K6:K126)
L1=SUMIF((\$C6:\$C126),\$H1,L6:L126)
M1=SUMIF((\$C6:\$C126),\$H1,M6:M126)
N1=SUMIF((\$C6:\$C126),\$H1,N6:N126)
O1=SUMIF((\$C6:\$C126),\$H1,O6:O126)
P1=SUMIF((\$C6:\$C126),\$H1,P6:P126)
Q1=SUMIF((\$C6:\$C126),\$H1,Q6:Q126)
R1=SUMIF((\$C6:\$C126),\$H1,R6:R126)

<tbody>
</tbody>

<tbody>
</tbody>

Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

RonB1111

Well-known Member
First, change the month entries in I5, J5, etc from 4, 5, etc to 4/1/2013, 5/1/2013, etc

Then enter this formula in I6 and copy down and across:
Code:
``=\$G6/\$F6*IF(AND(EOMONTH(\$E6,0<=EOMONTH(I\$5,0),EOMONTH(DATE(YEAR(\$E6),MONTH(\$E6)+\$F6,1),0)>=EOMONTH(I\$5,0)),1,0)*IF(EOMONTH(\$E6,0)=EOMONTH(I\$5,0),(EOMONTH(I\$5,0)-\$E6+1)/DAY(EOMONTH(I\$5,0)),1)*IF(EOMONTH(DATE(YEAR(\$E6),MONTH(\$E6)+\$F6,1),0)=EOMONTH(I\$5,0),(\$G6-SUM(\$H6:H6))/\$G6*\$F6,1)``

Sorry it's so long. Took a while just to get it to work and I ran out of time to try and shorten it.

Be aware that your sample had blank cells in H6:H98 and I used these cells in the formula so they must remain blank for the formula to work.

scott_bassman

New Member
First, change the month entries in I5, J5, etc from 4, 5, etc to 4/1/2013, 5/1/2013, etc

Then enter this formula in I6 and copy down and across:
Code:
``=\$G6/\$F6*IF(AND(EOMONTH(\$E6,0<=EOMONTH(I\$5,0),EOMONTH(DATE(YEAR(\$E6),MONTH(\$E6)+\$F6,1),0)>=EOMONTH(I\$5,0)),1,0)*IF(EOMONTH(\$E6,0)=EOMONTH(I\$5,0),(EOMONTH(I\$5,0)-\$E6+1)/DAY(EOMONTH(I\$5,0)),1)*IF(EOMONTH(DATE(YEAR(\$E6),MONTH(\$E6)+\$F6,1),0)=EOMONTH(I\$5,0),(\$G6-SUM(\$H6:H6))/\$G6*\$F6,1)``

Sorry it's so long. Took a while just to get it to work and I ran out of time to try and shorten it.

Be aware that your sample had blank cells in H6:H98 and I used these cells in the formula so they must remain blank for the formula to work.

Hi Ron, I'm not an expert in Excel, so it's possible that I'm doing something wrong. When I changed the I5, J5 etc from 4, 5, etc to 4/1/2013, 5/1/2013, etc I removed the formulas, and just plugged in the actual dates of 4/1/2013, 5/1/2013, etc.

Also, when entering the above code, I get the following error: "You've entered too many arguments for this function. To get help with this function, click OK to close this message. Then click the Insert Function button located to the left of the equal sign in your formula.

Thank you for your follow up and assistance - it's greatly appreciated!

RonB1111

Well-known Member
I accidentally deleted the 1st ) when I tried to remove a line break - here it is correctly. Copy the entire formula into I6 & let me know how it goes.
Code:
``=\$G6/\$F6*IF(AND(EOMONTH(\$E6,0)<=EOMONTH(I\$5,0),EOMONTH(DATE(YEAR(\$E6),MONTH(\$E6)+\$F6,1),0)>=EOMONTH(I\$5,0)),1,0)*IF(EOMONTH(\$E6,0)=EOMONTH(I\$5,0),(EOMONTH(I\$5,0)-\$E6+1)/DAY(EOMONTH(I\$5,0)),1)*IF(EOMONTH(DATE(YEAR(\$E6),MONTH(\$E6)+\$F6,1),0)=EOMONTH(I\$5,0),(\$G6-SUM(\$H6:H6))/\$G6*\$F6,1)``

scott_bassman

New Member
That worked perfectly! Now the fields, I4, J4, K4, etc. are not populating - there' a, "#DIV/O!" in each of them. If you could help with that too, that would be much appreciated.

RonB1111

Well-known Member
What are the formulas in I4, J4, etc? It would seem the formula should be =SUM(I1:I3) and copied across.

scott_bassman

New Member
What are the formulas in I4, J4, etc? It would seem the formula should be =SUM(I1:I3) and copied across.

Thank you so much! I really appreciate your assistance.

Thanks again!

RonB1111

Well-known Member
You're welcome. Thank you for providing feedback.

Replies
1
Views
153
Replies
4
Views
520
Replies
1
Views
162
Replies
1
Views
130
Replies
1
Views
94

1,195,939
Messages
6,012,420
Members
441,698
Latest member
DaveTeo

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.

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

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