Forum Discussion
Dynamic formula that splits/distributes revenue a value among various monthly start and end dates
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.png
John_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.
I apologize for any confusion.
A few things to note:
- All contracts have a start and an end date (although each one begins and ends at a different date)
- some contracts are longer than others ( 12 months vs 36 months )
-I would like the contract to be divided evenly through the start and end date
-think I'm getting closer with this formula if you can provide any insight:
=IF(AND(H$1<=E2,H$1>=$F2),SUM($D2/SUM(SUM($F2-$E2)+1)),"")Thanks in advance.
P.S. Riny_van_Eekelen
-on the file that you uploaded the start date in B1 starts 9/6/2020 however the formula does not begin to fill until Column Z or Oct-20 (* a month after)
-Also in column D, where was 30.41667 pulled from in the formula?
not sure why its not allowing me to upload the file but this is the complete table
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 Oct-21 Nov-21 Dec-21 Jan-22 Feb-22 Mar-22 Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Jan-23 Feb-23 Mar-23 Apr-23 May-23 Jun-23 Jul-23 Aug-23 Sep-23 Oct-23 Nov-23 Dec-23 Jan-24 Feb-24 Mar-24 Apr-24 May-24 Jun-24 Jul-24 Aug-24 Sep-24 Oct-24 Nov-24 Dec-24 $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 $99,450 03/31/20 03/31/23 $93,000 06/06/20 06/06/21 $91,125 11/25/20 11/25/23 $82,445 05/24/20 05/24/21 $82,500 04/25/21 04/25/24 $80,000 08/07/21 08/07/24 $76,250 05/23/19 05/23/22 $97,500 10/21/21 10/21/22 $53,365 01/31/19 01/31/21 $49,020 08/20/20 08/20/23 $42,525 01/30/19 01/30/22 $38,250 02/25/21 02/25/24 $33,225 10/25/21 10/25/23 $17,500 07/01/19 07/01/20 You wrote: "on the file that you uploaded the start date in B1 starts 9/6/2020 however the formula does not begin to fill until Column Z or Oct-20 (* a month after)"
Well, that's because you weren't clear. The formula I used splits the contract value into equal terms. If a 12 month contract starts and ends somewhere during a month, either the first or the last month will have to be skipped. Now I understand that your intention is to split it into 13 periods. A partial first month, then 11 whole months and a partial 12th month. But do you also want the 11 whole months to vary based on the number of days in each month?
Pasted your formula in my own file (with adjusted references) and it doesn't seem to make any sense. I must be doing something wrong. Can't really test it without seeing it at work in your file. Can you upload it or else share it via OneDrive or similar?
Hi to Answer your question even if the date encompasses 1 day out of the month, I would still like to include and round it up to the whole month and spread it across evenly.
this is the table Ive been trying to do the transfer test from, Column, 4 Row 2 formula: =IF(AND(D$10<=$B11,D$10>=$A11),SUM($C11/(SUM($B11-$A11)+1)),"")
Start Finish Value 24-Feb 25-Feb 26-Feb 27-Feb 28-Feb 1-Mar 2-Mar Mar-22 Mar-22 24-Feb 26-Feb 15.57 5.190 5.190 5.190 25-Feb 28-Feb 26.37 6.593 6.593 6.593 6.593 26-Feb 27-Feb 10 5.000 5.000 27-Feb 1-Mar 30.15 10.050 10.050 10.050 28-Feb 2-Mar 40.66 13.553 13.553 13.553 Feb-29 1-Mar 7.5 Is there a way to pull only the month from the various dates in the first 2 columns that include individual days to use across the other columns as just months?
Please see attached
Data:
https://docs.google.com/spreadsheets/d/1XpKT2NJCe0S8d9hbCcMlB1KwkHJtvi05/edit?usp=sharing&ouid=113886818105478529954&rtpof=true&sd=true
Example
https://docs.google.com/spreadsheets/d/1gN7PZASv7hUj4y-5bdnAP9KuK1XPMq4v/edit?usp=sharing&ouid=113886818105478529954&rtpof=true&sd=true