Forum Discussion
Dynamic formula that splits/distributes revenue a value among various monthly start and end dates
https://miro.medium.com/max/1400/0*6-oBlRyn-GVQcAPq.png **similar to this**
trying to figure out a single formula that will allow me to split up the value from Column A across columns B-Z with different start and end dates
| Contract Value | Start Date | End Date | Jan-19 | Feb-19 | Mar-19 | Apr-19 | May-19 | Jun-19 | Jul-19 | Aug-19 | Sep-19 | Oct-19 | Nov-19 | Dec-19 | Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 | Jul-20 | Aug-20 | Sep-20 | Oct-20 | Nov-20 | Dec-20 | Jan-21 | Feb-21 | Mar-21 | Apr-21 | May-21 | Jun-21 | Jul-21 | Aug-21 | Sep-21 | |
| $1,100,000 | 09/06/20 | 09/06/23 | ||||||||||||||||||||||||||||||||||
| $50,000 | 09/06/20 | NA | ||||||||||||||||||||||||||||||||||
| $375,000 | 05/28/20 | 05/28/21 | ||||||||||||||||||||||||||||||||||
| $172,475 | 02/03/21 | 02/03/22 | ||||||||||||||||||||||||||||||||||
| $231,750 | 08/07/19 | 08/07/22 | ||||||||||||||||||||||||||||||||||
| $191,250 | 06/26/20 | 06/26/21 | ||||||||||||||||||||||||||||||||||
| $162,560 | 12/08/20 | 12/08/23 | ||||||||||||||||||||||||||||||||||
| $127,475 | 12/30/20 | 12/30/21 | ||||||||||||||||||||||||||||||||||
| $212,500 | 03/07/21 | 03/07/24 | ||||||||||||||||||||||||||||||||||
| $122,060 | 07/25/20 | 07/25/23 | ||||||||||||||||||||||||||||||||||
| $104,125 | 04/26/19 | 04/26/22 |
Continued from here:
Thanks for starting a new thread.
Though, you haven't answered all of my questions yet. How would you treat a contract without an end date? And do I understand correctly that you now want to split the amounts into months taking the number of days per month into account? Or just determine that a contract covers 12 months and split it in 12 equal terms?
Just started one here: https://techcommunity.microsoft.com/t5/excel/dynamic-formula-that-splits-distributes-revenue-a-value...
Im using Office 365. sorry the columns would be A: CA. with defined start and end dates*Have been playing around with this formula but the days in each month are variable so 30 doesn't necessarily work, with the total falling slightly short.
=($E2<=H$1)*(H$1<=$F2)*($D2/(($F2-$E2)/30))or this
=IF(AND(H$1<=$F2,H$1>=$E2),SUM($D2/SUM(SUM($F2-$E2)+1)),"")
Trying to do something similar to this:
https://miro.medium.com/max/1400/0*6-oBlRyn-GVQcAPq.pngJohn_Bloggs650 No need to copy what's already in the other thread. And you aren't really answering my questions. But perhaps the attached file will help you on your way.
