computaplanet
New Member
- Joined
- Oct 15, 2011
- Messages
- 15
Hi all,
I would dearly love someone to help me with this, I am beginning to think that it is impossible to achieve and am on the verge of giving up, I have even done some creative thinking but I still come up short.... anyway...
Basically I have 2 tables, table one:
A21 - A32 start date
B21 - B32 End date
C21 - C32 (Blank for other reasons)
D21 - D32 Amount
Table 2
B27 - B39 Start Date
C27 - C39 End Date
D27 - D39 Number of days between start and end dates - (C-B = no.days)
E27 - E39 Amount in payment
F27 - E39 Daily amount - amount in payment / Number of days
Basically I have a formula in the Cells D21 to D32 which works exactly as it should with the exception that if my date range in table 2 expands across 2 date ranges in table one then the "remainder amount" doesnt get posted to the next correct cell ... so it just gets lost in cyber space example:
if the date range is 61 days which therefore spans 2 date ranges in table one. The formula splits the amount in table 2 and puts the first correct amount (31 days * £10 = £310 (worked out using the second table) within the correct cell in table 1, however the amount paid was £610 and so the remaining £300 should also be posted within the correct cell in table 1 that the date range from table 2 corresponds (usually the next one down).
I have kind of figured out how to get this to happen (in a roundabout fashion) however it still doesnt work:
I think if I add a helper table to get the formula to work out one column (as it does now) then work out the remaining amount place that in a second column and then user another Dynamic range to place them in the correct cells within the original table 1 (Column D21 - D32) of course you may see something which is much simpler which can achieve the same result - however this is the only way i can see that will actually allow me to
1. Figure out the remainder and place in the correct cell
2. Actually be able to add 2 or more amounts into the cells in table 1 (a seperate issue - the formula will at some point need to add 2 or maybe 3 amounts together in the same cell because the dates correspond
My problem is I cant figure out these dynamic ranges in the slightest, I can get simple forumlas but ones as long as the one I am using in D21-D32 I am totally lost with...
The formula I have which splits the amounts but loses the second amount is:
I hope I have made sense, it would be much easier to show you if I could place an attachment of the spreadsheet but this forum will not allow it for some reason? Please feel free to ask me any questions and thanks in advance for any help you can give to me...
I would dearly love someone to help me with this, I am beginning to think that it is impossible to achieve and am on the verge of giving up, I have even done some creative thinking but I still come up short.... anyway...
Basically I have 2 tables, table one:
A21 - A32 start date
B21 - B32 End date
C21 - C32 (Blank for other reasons)
D21 - D32 Amount
Table 2
B27 - B39 Start Date
C27 - C39 End Date
D27 - D39 Number of days between start and end dates - (C-B = no.days)
E27 - E39 Amount in payment
F27 - E39 Daily amount - amount in payment / Number of days
Basically I have a formula in the Cells D21 to D32 which works exactly as it should with the exception that if my date range in table 2 expands across 2 date ranges in table one then the "remainder amount" doesnt get posted to the next correct cell ... so it just gets lost in cyber space example:
if the date range is 61 days which therefore spans 2 date ranges in table one. The formula splits the amount in table 2 and puts the first correct amount (31 days * £10 = £310 (worked out using the second table) within the correct cell in table 1, however the amount paid was £610 and so the remaining £300 should also be posted within the correct cell in table 1 that the date range from table 2 corresponds (usually the next one down).
I have kind of figured out how to get this to happen (in a roundabout fashion) however it still doesnt work:
I think if I add a helper table to get the formula to work out one column (as it does now) then work out the remaining amount place that in a second column and then user another Dynamic range to place them in the correct cells within the original table 1 (Column D21 - D32) of course you may see something which is much simpler which can achieve the same result - however this is the only way i can see that will actually allow me to
1. Figure out the remainder and place in the correct cell
2. Actually be able to add 2 or more amounts into the cells in table 1 (a seperate issue - the formula will at some point need to add 2 or maybe 3 amounts together in the same cell because the dates correspond
My problem is I cant figure out these dynamic ranges in the slightest, I can get simple forumlas but ones as long as the one I am using in D21-D32 I am totally lost with...
The formula I have which splits the amounts but loses the second amount is:
Code:
=SUMPRODUCT(($A21<=$B$37:$B$49)*($B21>=$B$37:$B$49)*($B21>=$C$37:$C$49)*$E$37:$E$49)+SUMPRODUCT(($A21<=$B$37:$B$49)*($B21>=$B$37:$B$49)*($B21<$C$37:$C$49)*(($B21-$B$37:$B$49)*$F$37:$F$49+$E$37:$E$49/$D$37:$D$49))+SUMPRODUCT($B21<$B$37:$B$49*$D22)
I hope I have made sense, it would be much easier to show you if I could place an attachment of the spreadsheet but this forum will not allow it for some reason? Please feel free to ask me any questions and thanks in advance for any help you can give to me...