Hey all,
I am having trouble to come up with a formula to auto-populate the split amount based on the starting period (Column A) and ending period (Column B)
I have been able to split the amount with formula, however, I would like to Transfer them to the respective periods in column H - column V based on the period that they fall into.
Could you guys help me to come up with a formula to Auto-populate the sheet?
Many thanks in advance~!
A B C E F
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
in E2 = IF(IF(MONTH(A2)<>MONTH(B2);C2/(B2-A2)*DAY(EOMONTH(A2;0)-DAY(A2)));IF(MONTH(A2)<>MONTH(B2);C2/(B2-A2)*DAY(EOMONTH(A2;0)-DAY(A2)));O2)
in F2 =C2/(B2-A2)*DAY(B2)
<colgroup><col span="3"><col span="12"></colgroup><tbody>
</tbody>
The ones that are marked in red are the expected results
I am having trouble to come up with a formula to auto-populate the split amount based on the starting period (Column A) and ending period (Column B)
I have been able to split the amount with formula, however, I would like to Transfer them to the respective periods in column H - column V based on the period that they fall into.
Could you guys help me to come up with a formula to Auto-populate the sheet?
Many thanks in advance~!
A B C E F
Period Start | Period End | Amount | 1st period | 2nd period | |
10/12/2017 | 09/01/2018 | 70.525,00 | 49367,50 | 21157,50 | |
11/12/2017 | 10/01/2018 | 30.000,00 | 20000,00 | 10000,00 | |
21/11/2017 | 20/12/2017 | 57.000,00 | 17689,66 | 39310,34 | |
26/12/2017 | 25/01/2018 | 241.800,00 | 40300,00 | 201500,00 | |
17/12/2017 | 16/01/2018 | 89.900,00 | 41953,33 | 47946,67 | |
30/12/2017 | 29/01/2018 | 69.750,00 | 2325,00 | 67425,00 | |
29/12/2017 | 28/01/2018 | 71.300,00 | 4753,33 | 66546,67 | |
03/01/2018 | 02/02/2018 | 60.140,00 | 56130,67 | 4009,33 | |
08/12/2017 | 07/01/2018 | 58.280,00 | 44681,33 | 13598,67 | |
09/12/2017 | 08/01/2018 | 62.000,00 | 45466,67 | 16533,33 | |
09/12/2017 | 08/01/2018 | 69.750,00 | 51150,00 | 18600,00 | |
07/12/2017 | 06/01/2018 | 65.100,00 | 52080,00 | 13020,00 | |
23/12/2017 | 22/01/2018 | 40.145,00 | 10705,33 | 29439,67 | |
07/12/2017 | 06/01/2018 | 52.000,00 | 41600,00 | 10400,00 | |
24/12/2017 | 23/01/2018 | 50.187,22 | 11710,35 | 38476,87 | |
23/12/2017 | 22/01/2018 | 42.375,37 | 11300,10 | 31075,27 | |
09/12/2017 | 08/01/2018 | 9.401,00 | 6894,07 | 2506,93 | |
14/12/2017 | 13/01/2018 | 35.852,06 | 20316,17 | 15535,89 | |
23/12/2017 | 22/01/2018 | 31.000,00 | 8266,67 | 22733,33 | |
01/12/2017 | 31/12/2017 | 68.200,00 | 0,00 | 70473,33 | |
11/12/2017 | 10/01/2018 | 56.265,00 | 37510,00 | 18755,00 | |
20/12/2017 | 19/01/2018 | 88.350,00 | 32395,00 | 55955,00 | |
29/12/2017 | 28/01/2018 | 170.283,59 | 11352,24 | 158931,35 | |
18/12/2017 | 17/01/2018 | 12.400,00 | 5373,33 | 7026,67 | |
13/12/2017 | 12/01/2018 | 107.100,00 | 64260,00 | 42840,00 |
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
in E2 = IF(IF(MONTH(A2)<>MONTH(B2);C2/(B2-A2)*DAY(EOMONTH(A2;0)-DAY(A2)));IF(MONTH(A2)<>MONTH(B2);C2/(B2-A2)*DAY(EOMONTH(A2;0)-DAY(A2)));O2)
in F2 =C2/(B2-A2)*DAY(B2)
01/11/2017 | 01/12/2017 | 01/01/2018 | 01/02/2018 | 01/03/2018 | 01/04/2018 | 01/05/2018 | 01/06/2018 | 01/07/2018 | 01/08/2018 | 01/09/2018 | 01/10/2018 | 01/11/2018 | 01/12/2018 | 01/01/2019 |
49367,5 | 21157,5 | |||||||||||||
20000 | 10000 | |||||||||||||
17689,65517 | 39310,34483 | |||||||||||||
40300 | 201500 | |||||||||||||
41953,33333 | 47946,66667 | |||||||||||||
<colgroup><col span="3"><col span="12"></colgroup><tbody>
</tbody>
The ones that are marked in red are the expected results