For completeness and understanding can you supply for your sample the values you would expect in j2:j5
I am guessing
j2 (30 * 20.41) = 612.30
J3 (31 * 48.39) + j2 = 2112.39
J4 (30 * 23.53) + j3 = 2818.29
Hi there,
I've tried to recreate two tables which I'm working with below:
Start Date Finish Date Effective Days Total Amount Daily Amount Month Start of Month End of Month Monthly Amount 04/09/2019 22/10/2019 49 1000 20.41 Sep-19 01/09/2019 30/09/2019 ??? 15/09/2019 15/10/2019 31 1500 48.39 Oct-19 01/10/2019 31/10/2019 ??? 01/10/2019 24/12/2019 85 2000 23.53 Nov-19 01/11/2019 30/11/2019 ??? Dec-10 01/12/2019 31/12/2019 ???
For the sake of this exercise, the first table would be A1:E4 (including headers) and the second table would be G1:J5.
I would like to have a formula which fills in the J column. This would have to go row-by-row in the first table, calculating how many days fall between the Start of Month and End of Month in the second table, multiply that by the amount in the Daily Amount column, then add that to the loop down the rest of the rows.
This is just a sample; unfortunately my real data is much larger!
I've tried to name the topic because I'm pretty confident it'll need an array formula, but I'm quite happy to accept a non-array formula if one exists. It might not be a SUMIFS either which works; that's fine too.
Please help!
For completeness and understanding can you supply for your sample the values you would expect in j2:j5
I am guessing
j2 (30 * 20.41) = 612.30
J3 (31 * 48.39) + j2 = 2112.39
J4 (30 * 23.53) + j3 = 2818.29
Thanks for the reply Jim! Not quite - here's a breakdown below.
J2 I'd expect 1,325.21
551.02 from the A2:E2 row (27 days @ 20.41), plus,
774.19 from the A3:E3 row (16 days @ 48.39), plus,
0 from the A4:E4 row
J3 I'd expect 1,904.20
448.98 from the A2:E2 row (22 days @ 20.41), plus,
725.81 from the A3:E3 row (15 days @ 48.39), plus,
729.41 from the A4:E4 row (31 days @ 23.54)
J4 I'd expect 704.88
0 from the A2:E2 row, plus,
0 from the A3:E3 row, plus,
705.88 from the A4:E4 row (30 days @ 23.54)
J5 I'd expect 564.71
0 from the A2:E2 row, plus,
0 from the A3:E3 row, plus,
564.71 from the A4:E4 row (24 days @ 23.54)
That would equal to 4,500 in total (1,325.21 + 1,904.20 + 705.88 + 564.71).
I've not got a solution, but I've created a formula which would solve just for J2 as above.
It looks complex but really all it does is run through this logic for a single month (Sept 2019 in this example):=(IF(IF(AND(A2>=H$2,B2<=I$2),TRUE,FALSE)=TRUE,B2-A2+1,IF(IF(AND(A2 =H$2,A2<=I$2,B2>I$2),TRUE,FALSE)=TRUE,I$2-A2+1,IF(IF(AND(A2 I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E2)+(IF(IF(AND(A3>=H$2,B3<=I$2),TRUE,FALSE)=TRUE,B3-A3+1,IF(IF(AND(A3 =H$2,A3<=I$2,B3>I$2),TRUE,FALSE)=TRUE,I$2-A3+1,IF(IF(AND(A3 I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E3)+(IF(IF(AND(A4>=H$2,B4<=I$2),TRUE,FALSE)=TRUE,B4-A4+1,IF(IF(AND(A4 =H$2,A4<=I$2,B4>I$2),TRUE,FALSE)=TRUE,I$2-A4+1,IF(IF(AND(A4 I$2),TRUE,FALSE)=TRUE,I$2-H$2+1,0))))*E4)
If A2 and B2 are within the month, just use the difference between A2 and B2 to calculate the number of days needed to multiply by E2
> If A2 is smaller than H2, and B2 is smaller than I2, use the difference between B2 and H2 as the number of days needed to multiple by E2
>> If A2 is larger than H2 and smaller than I2, but B2 is larger than I2, use the difference between A2 and I2 as the number of days needed to multiply by E2
>>> If A2 is smaller than H2 and B2 is larger than I2, use the difference between H2 and I2 as the number of days needed to multiple by E2
>>>> If none of the above apply then there are 0 days in the month that match
I just don't really know how to turn this into an array to go through columns A and B, one at a time and multiply the results by column E, relative to the dates which are in columns H and I.
Hi, here is an option that you can try:
Excel 2013/2016
A B C D E F G H I J 1 Start Date Finish Date Effective Days Total Amount Daily Amount Month Start of Month End of Month Monthly Amount 2 04/09/2019 22/10/2019 49 1000 20.41 01/09/2019 01/09/2019 30/09/2019 1325.31 3 15/09/2019 15/10/2019 31 1500 48.39 01/10/2019 01/10/2019 31/10/2019 1904.3 4 01/10/2019 24/12/2019 85 2000 23.53 01/11/2019 01/11/2019 30/11/2019 705.9 5 01/12/2010 01/12/2019 31/12/2019 564.72 Sheet1
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell Formula J2 {=SUM(IF($B$2:$B$4>=H2,IF($A$2:$A$4<=I2,$E$2:$E$4*(1+IF($B$2:$B$4>=I2,I2,$B$2:$B$4)-IF($A$2:$A$4<=H2,H2,$A$2:$A$4)))))}
Note: Do not try and enter the {} manually yourself
[code]your code[/code]
Like this thread? Share it with others