# Dynamic ranges do not work as they should

#### computaplanet

##### New Member
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:

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...

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
The description has the two tables overlapping and the formula references other cells.

Could you post both of your tables? That would go a long way in helping to better understand your problem and it allows us to copy the data and paste it in excel for use.

MrExcel HTML Maker or Excel Jeanie

Apologies for confusion of the cell numbers I dont know what I was typing when i put those cell references in...

Table 1
HTML:
``[RANGE=cls:xl2bb-100][XR][XH=cs:6]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][/XR][XR][XH]20[/XH][XD=h:c|fw:b|bc:ffff99]PCM START DATE (DUE)[/XD][XD=h:c|fw:b|bc:ffff99]PCM END DATE FOR CALANDAR MONTH[/XD][XD=h:c|fw:b|bc:c5d9f1]Rent PCM Due[/XD][XD=h:c|fw:b|bc:f2dddc]HB Payment[/XD][XD=h:c|fw:b|bc:99cc00]Top up[/XD][/XR][XR][XH]21[/XH][XD=h:c]01/01/2011[/XD][XD=h:c]31/01/2011[/XD][XD=h:c]0[/XD][XD=h:c] 310.00[/XD][XD=h:r] -[/XD][/XR][XR][XH]22[/XH][XD=h:c]01/02/2011[/XD][XD=h:c]28/02/2011[/XD][XD=h:c]0[/XD][XD=h:c] 280.00[/XD][XD=h:r] -[/XD][/XR][XR][XH]23[/XH][XD=h:c]01/03/2011[/XD][XD=h:c]31/03/2011[/XD][XD=h:c]0[/XD][XD=h:c] 310.00[/XD][XD=h:r] -[/XD][/XR][XR][XH]24[/XH][XD=h:c]01/04/2011[/XD][XD=h:c]30/04/2011[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]25[/XH][XD=h:c]01/05/2011[/XD][XD=h:c]31/05/2011[/XD][XD=h:c]0[/XD][XD=h:c] 300.00[/XD][XD=h:r] -[/XD][/XR][XR][XH]26[/XH][XD=h:c]01/06/2011[/XD][XD=h:c]30/06/2011[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]27[/XH][XD=h:c]01/07/2011[/XD][XD=h:c]31/07/2011[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]28[/XH][XD=h:c]01/08/2011[/XD][XD=h:c]31/08/2011[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]29[/XH][XD=h:c]01/09/2011[/XD][XD=h:c]30/09/2011[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]30[/XH][XD=h:c]01/10/2011[/XD][XD=h:c]31/10/2011[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]31[/XH][XD=h:c]01/11/2011[/XD][XD=h:c]30/11/2011[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]32[/XH][XD=h:c]01/12/2011[/XD][XD=h:c]31/12/2011[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]33[/XH][XD=h:c|fw:b]Totals[/XD][XD][/XD][XD=h:r|fw:b] -[/XD][XD=h:r|fw:b] 1,200.00[/XD][XD=h:r|fw:b] -[/XD][/XR][XR][XH]34[/XH][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD=h:r|fw:b] -[/XD][/XR][XR][XH=cs:6][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][B]Excel 2007[/B][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2]Cell Formulas[/XH][/XR][XR][XD]Cell[/XD][XD]Formula[/XD][/XR][XR][XD]A21[/XD][XD]=D15[/XD][/XR][XR][XD]A22[/XD][XD]=DATE(YEAR(A21),MONTH(A21)+1,DAY(A21))[/XD][/XR][XR][XD]A23[/XD][XD]=DATE(YEAR(A22),MONTH(A22)+1,DAY(A22))[/XD][/XR][XR][XD]A24[/XD][XD]=DATE(YEAR(A23),MONTH(A23)+1,DAY(A23))[/XD][/XR][XR][XD]A25[/XD][XD]=DATE(YEAR(A24),MONTH(A24)+1,DAY(A24))[/XD][/XR][XR][XD]A26[/XD][XD]=DATE(YEAR(A25),MONTH(A25)+1,DAY(A25))[/XD][/XR][XR][XD]A27[/XD][XD]=DATE(YEAR(A26),MONTH(A26)+1,DAY(A26))[/XD][/XR][XR][XD]A28[/XD][XD]=DATE(YEAR(A27),MONTH(A27)+1,DAY(A27))[/XD][/XR][XR][XD]A29[/XD][XD]=DATE(YEAR(A28),MONTH(A28)+1,DAY(A28))[/XD][/XR][XR][XD]A30[/XD][XD]=DATE(YEAR(A29),MONTH(A29)+1,DAY(A29))[/XD][/XR][XR][XD]A31[/XD][XD]=DATE(YEAR(A30),MONTH(A30)+1,DAY(A30))[/XD][/XR][XR][XD]A32[/XD][XD]=DATE(YEAR(A31),MONTH(A31)+1,DAY(A31))[/XD][/XR][XR][XD]B21[/XD][XD]=DATE(YEAR(A21),MONTH(A21)+1,DAY(A21)-1)[/XD][/XR][XR][XD]B22[/XD][XD]=DATE(YEAR(A22),MONTH(A22)+1,DAY(A22)-1)[/XD][/XR][XR][XD]B23[/XD][XD]=DATE(YEAR(A23),MONTH(A23)+1,DAY(A23)-1)[/XD][/XR][XR][XD]B24[/XD][XD]=DATE(YEAR(A24),MONTH(A24)+1,DAY(A24)-1)[/XD][/XR][XR][XD]B25[/XD][XD]=DATE(YEAR(A25),MONTH(A25)+1,DAY(A25)-1)[/XD][/XR][XR][XD]B26[/XD][XD]=DATE(YEAR(A26),MONTH(A26)+1,DAY(A26)-1)[/XD][/XR][XR][XD]B27[/XD][XD]=DATE(YEAR(A27),MONTH(A27)+1,DAY(A27)-1)[/XD][/XR][XR][XD]B28[/XD][XD]=DATE(YEAR(A28),MONTH(A28)+1,DAY(A28)-1)[/XD][/XR][XR][XD]B29[/XD][XD]=DATE(YEAR(A29),MONTH(A29)+1,DAY(A29)-1)[/XD][/XR][XR][XD]B30[/XD][XD]=DATE(YEAR(A30),MONTH(A30)+1,DAY(A30)-1)[/XD][/XR][XR][XD]B31[/XD][XD]=DATE(YEAR(A31),MONTH(A31)+1,DAY(A31)-1)[/XD][/XR][XR][XD]B32[/XD][XD]=DATE(YEAR(A32),MONTH(A32)+1,DAY(A32)-1)[/XD][/XR][XR][XD]C21[/XD][XD]=IF(AND( \$AY\$1 >= A21, \$D\$14 >= A21), \$B\$17,"0")[/XD][/XR][XR][XD]C22[/XD][XD]=IF(AND( \$AY\$1 >= A22, \$D\$14<= A22), \$B\$17,"0")[/XD][/XR][XR][XD]C23[/XD][XD]=IF(AND( \$AY\$1 >= A23, \$D\$14<= A23), \$B\$17,"0")[/XD][/XR][XR][XD]C24[/XD][XD]=IF(AND( \$AY\$1 >= A24, \$D\$14<= A24), \$B\$17,"0")[/XD][/XR][XR][XD]C25[/XD][XD]=IF(AND( \$AY\$1 >= A25, \$D\$14<= A25), \$B\$17,"0")[/XD][/XR][XR][XD]C26[/XD][XD]=IF(AND( \$AY\$1 >= A26, \$D\$14<= A26), \$B\$17,"0")[/XD][/XR][XR][XD]C27[/XD][XD]=IF(AND( \$AY\$1 >= A27, \$D\$14<= A27), \$B\$17,"0")[/XD][/XR][XR][XD]C28[/XD][XD]=IF(AND( \$AY\$1 >= A28, \$D\$14<= A28), \$B\$17,"0")[/XD][/XR][XR][XD]C29[/XD][XD]=IF(AND( \$AY\$1 >= A29, \$D\$14<= A29), \$B\$17,"0")[/XD][/XR][XR][XD]C30[/XD][XD]=IF(AND( \$AY\$1 >= A30, \$D\$14<= A30), \$B\$17,"0")[/XD][/XR][XR][XD]C31[/XD][XD]=IF(AND( \$AY\$1 >= A31, \$D\$14<= A31), \$B\$17,"0")[/XD][/XR][XR][XD]C32[/XD][XD]=IF(AND( \$AY\$1 >= A32, \$D\$14<= A32), \$B\$17,"0")[/XD][/XR][XR][XD]C33[/XD][XD]=SUM(C21:C32)[/XD][/XR][XR][XD]D21[/XD][XD]=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)[/XD][/XR][XR][XD]D22[/XD][XD]=SUMPRODUCT((\$A22<=\$B\$37:\$B\$49)*(\$B22>=\$B\$37:\$B\$49)*(\$B22>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A22<=\$B\$37:\$B\$49)*(\$B22>=\$B\$37:\$B\$49)*(\$B22<\$C\$37:\$C\$49)*((\$B22-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B22<\$B\$37:\$B\$49*\$D23)[/XD][/XR][XR][XD]D23[/XD][XD]=SUMPRODUCT((\$A23<=\$B\$37:\$B\$49)*(\$B23>=\$B\$37:\$B\$49)*(\$B23>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A23<=\$B\$37:\$B\$49)*(\$B23>=\$B\$37:\$B\$49)*(\$B23<\$C\$37:\$C\$49)*((\$B23-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B23<\$B\$37:\$B\$49*\$D24)[/XD][/XR][XR][XD]D24[/XD][XD]=SUMPRODUCT((\$A24<=\$B\$37:\$B\$49)*(\$B24>=\$B\$37:\$B\$49)*(\$B24>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A24<=\$B\$37:\$B\$49)*(\$B24>=\$B\$37:\$B\$49)*(\$B24<\$C\$37:\$C\$49)*((\$B24-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B24<\$B\$37:\$B\$49*\$D25)[/XD][/XR][XR][XD]D25[/XD][XD]=SUMPRODUCT((\$A25<=\$B\$37:\$B\$49)*(\$B25>=\$B\$37:\$B\$49)*(\$B25>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A25<=\$B\$37:\$B\$49)*(\$B25>=\$B\$37:\$B\$49)*(\$B25<\$C\$37:\$C\$49)*((\$B25-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B25<\$B\$37:\$B\$49*\$D26)[/XD][/XR][XR][XD]D26[/XD][XD]=SUMPRODUCT((\$A26<=\$B\$37:\$B\$49)*(\$B26>=\$B\$37:\$B\$49)*(\$B26>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A26<=\$B\$37:\$B\$49)*(\$B26>=\$B\$37:\$B\$49)*(\$B26<\$C\$37:\$C\$49)*((\$B26-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B26<\$B\$37:\$B\$49*\$D27)[/XD][/XR][XR][XD]D27[/XD][XD]=SUMPRODUCT((\$A27<=\$B\$37:\$B\$49)*(\$B27>=\$B\$37:\$B\$49)*(\$B27>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A27<=\$B\$37:\$B\$49)*(\$B27>=\$B\$37:\$B\$49)*(\$B27<\$C\$37:\$C\$49)*((\$B27-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B27<\$B\$37:\$B\$49*\$D28)[/XD][/XR][XR][XD]D28[/XD][XD]=SUMPRODUCT((\$A28<=\$B\$37:\$B\$49)*(\$B28>=\$B\$37:\$B\$49)*(\$B28>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A28<=\$B\$37:\$B\$49)*(\$B28>=\$B\$37:\$B\$49)*(\$B28<\$C\$37:\$C\$49)*((\$B28-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B28<\$B\$37:\$B\$49*\$D29)[/XD][/XR][XR][XD]D29[/XD][XD]=SUMPRODUCT((\$A29<=\$B\$37:\$B\$49)*(\$B29>=\$B\$37:\$B\$49)*(\$B29>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A29<=\$B\$37:\$B\$49)*(\$B29>=\$B\$37:\$B\$49)*(\$B29<\$C\$37:\$C\$49)*((\$B29-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B29<\$B\$37:\$B\$49*\$D30)[/XD][/XR][XR][XD]D30[/XD][XD]=SUMPRODUCT((\$A30<=\$B\$37:\$B\$49)*(\$B30>=\$B\$37:\$B\$49)*(\$B30>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A30<=\$B\$37:\$B\$49)*(\$B30>=\$B\$37:\$B\$49)*(\$B30<\$C\$37:\$C\$49)*((\$B30-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B30<\$B\$37:\$B\$49*\$D31)[/XD][/XR][XR][XD]D31[/XD][XD]=SUMPRODUCT((\$A31<=\$B\$37:\$B\$49)*(\$B31>=\$B\$37:\$B\$49)*(\$B31>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A31<=\$B\$37:\$B\$49)*(\$B31>=\$B\$37:\$B\$49)*(\$B31<\$C\$37:\$C\$49)*((\$B31-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B31<\$B\$37:\$B\$49*\$D32)[/XD][/XR][XR][XD]D32[/XD][XD]=SUMPRODUCT((\$A32<=\$B\$37:\$B\$49)*(\$B32>=\$B\$37:\$B\$49)*(\$B32>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A32<=\$B\$37:\$B\$49)*(\$B32>=\$B\$37:\$B\$49)*(\$B32<\$C\$37:\$C\$49)*((\$B32-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))[/XD][/XR][XR][XD]D33[/XD][XD]=SUM(D21:D32)[/XD][/XR][XR][XD]E21[/XD][XD]=C21-BV21[/XD][/XR][XR][XD]E22[/XD][XD]=C22-BV22[/XD][/XR][XR][XD]E23[/XD][XD]=C23-BV23[/XD][/XR][XR][XD]E24[/XD][XD]=C24-BV24[/XD][/XR][XR][XD]E25[/XD][XD]=C25-BV25[/XD][/XR][XR][XD]E26[/XD][XD]=C26-BV26[/XD][/XR][XR][XD]E27[/XD][XD]=C27-BV27[/XD][/XR][XR][XD]E28[/XD][XD]=C28-BV28[/XD][/XR][XR][XD]E29[/XD][XD]=C29-BV29[/XD][/XR][XR][XD]E30[/XD][XD]=C30-BV30[/XD][/XR][XR][XD]E31[/XD][XD]=C31-BV31[/XD][/XR][XR][XD]E32[/XD][XD]=C32-BV32[/XD][/XR][XR][XD]E33[/XD][XD]=SUM(E21:E32)[/XD][/XR][XR][XD]E34[/XD][XD]=E33+BV33[/XD][/XR][/RANGE]``

Table 2
HTML:
``[RANGE=cls:xl2bb-100][XR][XH=cs:7]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][/XR][XR][XH]36[/XH][XD=h:c|fw:b|bc:ccffcc]HB Payments Reference[/XD][XD=h:c|fw:b|bc:ccffcc]Period Begin[/XD][XD=h:c|fw:b|bc:ccffcc]Period End[/XD][XD=h:c|fw:b|bc:ccffcc]Number Days in period[/XD][XD=h:c|fw:b|bc:ccffcc]Total Amount of Payment[/XD][XD=h:c|fw:b|bc:ccffcc]Daily amount paid by HB[/XD][/XR][XR][XH]37[/XH][XD][/XD][XD=h:c]01/01/2011[/XD][XD=h:c]31/01/2011[/XD][XD=h:c]31[/XD][XD=h:c] 310.00[/XD][XD=h:c] 10.00[/XD][/XR][XR][XH]38[/XH][XD][/XD][XD=h:c]01/02/2011[/XD][XD=h:c]28/02/2011[/XD][XD=h:c]28[/XD][XD=h:c] 280.00[/XD][XD=h:c] 10.00[/XD][/XR][XR][XH]39[/XH][XD][/XD][XD=h:c]01/03/2011[/XD][XD=h:c]30/04/2011[/XD][XD=h:c]61[/XD][XD=h:c] 610.00[/XD][XD=h:c] 10.00[/XD][/XR][XR][XH]40[/XH][XD][/XD][XD=h:c]01/05/2011[/XD][XD=h:c]30/05/2011[/XD][XD=h:c]30[/XD][XD=h:c] 300.00[/XD][XD=h:c] 10.00[/XD][/XR][XR][XH]41[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c]1[/XD][XD=h:c] -[/XD][XD=h:c] -[/XD][/XR][XR][XH]42[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c]1[/XD][XD=h:c] -[/XD][XD=h:c] -[/XD][/XR][XR][XH]43[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c]1[/XD][XD=h:c] -[/XD][XD=h:c] -[/XD][/XR][XR][XH]44[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c]1[/XD][XD=h:c] -[/XD][XD=h:c] -[/XD][/XR][XR][XH]45[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c]1[/XD][XD=h:c] -[/XD][XD=h:c] -[/XD][/XR][XR][XH]46[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c]1[/XD][XD=h:c] -[/XD][XD=h:c] -[/XD][/XR][XR][XH]47[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c]1[/XD][XD=h:c] -[/XD][XD=h:c] -[/XD][/XR][XR][XH]48[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c]1[/XD][XD=h:c] -[/XD][XD=h:c] -[/XD][/XR][XR][XH]49[/XH][XD][/XD][XD][/XD][XD][/XD][XD=h:c]1[/XD][XD=h:c] -[/XD][XD=h:c] -[/XD][/XR][XR][XH=cs:7][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][B]Excel 2007[/B][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2]Cell Formulas[/XH][/XR][XR][XD]Cell[/XD][XD]Formula[/XD][/XR][XR][XD]D37[/XD][XD]=C37-B37+1[/XD][/XR][XR][XD]D38[/XD][XD]=C38-B38+1[/XD][/XR][XR][XD]D39[/XD][XD]=C39-B39+1[/XD][/XR][XR][XD]D40[/XD][XD]=C40-B40+1[/XD][/XR][XR][XD]D41[/XD][XD]=C41-B41+1[/XD][/XR][XR][XD]D42[/XD][XD]=C42-B42+1[/XD][/XR][XR][XD]D43[/XD][XD]=C43-B43+1[/XD][/XR][XR][XD]D44[/XD][XD]=C44-B44+1[/XD][/XR][XR][XD]D45[/XD][XD]=C45-B45+1[/XD][/XR][XR][XD]D46[/XD][XD]=C46-B46+1[/XD][/XR][XR][XD]D47[/XD][XD]=C47-B47+1[/XD][/XR][XR][XD]D48[/XD][XD]=C48-B48+1[/XD][/XR][XR][XD]D49[/XD][XD]=C49-B49+1[/XD][/XR][XR][XD]F37[/XD][XD]=IF(E37=0,0,E37/D37)[/XD][/XR][XR][XD]F38[/XD][XD]=IF(E38=0,0,E38/D38)[/XD][/XR][XR][XD]F39[/XD][XD]=IF(E39=0,0,E39/D39)[/XD][/XR][XR][XD]F40[/XD][XD]=IF(E40=0,0,E40/D40)[/XD][/XR][XR][XD]F41[/XD][XD]=IF(E41=0,0,E41/D41)[/XD][/XR][XR][XD]F42[/XD][XD]=IF(E42=0,0,E42/D42)[/XD][/XR][XR][XD]F43[/XD][XD]=IF(E43=0,0,E43/D43)[/XD][/XR][XR][XD]F44[/XD][XD]=IF(E44=0,0,E44/D44)[/XD][/XR][XR][XD]F45[/XD][XD]=IF(E45=0,0,E45/D45)[/XD][/XR][XR][XD]F46[/XD][XD]=IF(E46=0,0,E46/D46)[/XD][/XR][XR][XD]F47[/XD][XD]=IF(E47=0,0,E47/D47)[/XD][/XR][XR][XD]F48[/XD][XD]=IF(E48=0,0,E48/D48)[/XD][/XR][XR][XD]F49[/XD][XD]=IF(E49=0,0,E49/D49)[/XD][/XR][/RANGE]``

Table 1
Excel Workbook
ABCDE
20PCM START DATE (DUE)PCM END DATE FOR CALANDAR MONTHRent PCM DueHB PaymentTop up
2101/01/201131/01/20110 310.00 -
2201/02/201128/02/20110 280.00 -
2301/03/201131/03/20110 310.00 -
2401/04/201130/04/20110 - -
2501/05/201131/05/20110 300.00 -
2601/06/201130/06/20110 - -
2701/07/201131/07/20110 - -
2801/08/201131/08/20110 - -
2901/09/201130/09/20110 - -
3001/10/201131/10/20110 - -
3101/11/201130/11/20110 - -
3201/12/201131/12/20110 - -
33Totals - 1,200.00 -
34 -
Sheet1
Excel 2007
Cell Formulas
RangeFormula
A21=D15
A22=DATE(YEAR(A21),MONTH(A21)+1,DAY(A21))
A23=DATE(YEAR(A22),MONTH(A22)+1,DAY(A22))
A24=DATE(YEAR(A23),MONTH(A23)+1,DAY(A23))
A25=DATE(YEAR(A24),MONTH(A24)+1,DAY(A24))
A26=DATE(YEAR(A25),MONTH(A25)+1,DAY(A25))
A27=DATE(YEAR(A26),MONTH(A26)+1,DAY(A26))
A28=DATE(YEAR(A27),MONTH(A27)+1,DAY(A27))
A29=DATE(YEAR(A28),MONTH(A28)+1,DAY(A28))
A30=DATE(YEAR(A29),MONTH(A29)+1,DAY(A29))
A31=DATE(YEAR(A30),MONTH(A30)+1,DAY(A30))
A32=DATE(YEAR(A31),MONTH(A31)+1,DAY(A31))
B21=DATE(YEAR(A21),MONTH(A21)+1,DAY(A21)-1)
B22=DATE(YEAR(A22),MONTH(A22)+1,DAY(A22)-1)
B23=DATE(YEAR(A23),MONTH(A23)+1,DAY(A23)-1)
B24=DATE(YEAR(A24),MONTH(A24)+1,DAY(A24)-1)
B25=DATE(YEAR(A25),MONTH(A25)+1,DAY(A25)-1)
B26=DATE(YEAR(A26),MONTH(A26)+1,DAY(A26)-1)
B27=DATE(YEAR(A27),MONTH(A27)+1,DAY(A27)-1)
B28=DATE(YEAR(A28),MONTH(A28)+1,DAY(A28)-1)
B29=DATE(YEAR(A29),MONTH(A29)+1,DAY(A29)-1)
B30=DATE(YEAR(A30),MONTH(A30)+1,DAY(A30)-1)
B31=DATE(YEAR(A31),MONTH(A31)+1,DAY(A31)-1)
B32=DATE(YEAR(A32),MONTH(A32)+1,DAY(A32)-1)
C21=IF(AND( \$AY\$1 >= A21, \$D\$14 >= A21), \$B\$17,"0")
C22=IF(AND( \$AY\$1 >= A22, \$D\$14<= A22), \$B\$17,"0")
C23=IF(AND( \$AY\$1 >= A23, \$D\$14<= A23), \$B\$17,"0")
C24=IF(AND( \$AY\$1 >= A24, \$D\$14<= A24), \$B\$17,"0")
C25=IF(AND( \$AY\$1 >= A25, \$D\$14<= A25), \$B\$17,"0")
C26=IF(AND( \$AY\$1 >= A26, \$D\$14<= A26), \$B\$17,"0")
C27=IF(AND( \$AY\$1 >= A27, \$D\$14<= A27), \$B\$17,"0")
C28=IF(AND( \$AY\$1 >= A28, \$D\$14<= A28), \$B\$17,"0")
C29=IF(AND( \$AY\$1 >= A29, \$D\$14<= A29), \$B\$17,"0")
C30=IF(AND( \$AY\$1 >= A30, \$D\$14<= A30), \$B\$17,"0")
C31=IF(AND( \$AY\$1 >= A31, \$D\$14<= A31), \$B\$17,"0")
C32=IF(AND( \$AY\$1 >= A32, \$D\$14<= A32), \$B\$17,"0")
C33=SUM(C21:C32)
D21=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)
D22=SUMPRODUCT((\$A22<=\$B\$37:\$B\$49)*(\$B22>=\$B\$37:\$B\$49)*(\$B22>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A22<=\$B\$37:\$B\$49)*(\$B22>=\$B\$37:\$B\$49)*(\$B22<\$C\$37:\$C\$49)*((\$B22-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B22<\$B\$37:\$B\$49*\$D23)
D23=SUMPRODUCT((\$A23<=\$B\$37:\$B\$49)*(\$B23>=\$B\$37:\$B\$49)*(\$B23>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A23<=\$B\$37:\$B\$49)*(\$B23>=\$B\$37:\$B\$49)*(\$B23<\$C\$37:\$C\$49)*((\$B23-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B23<\$B\$37:\$B\$49*\$D24)
D24=SUMPRODUCT((\$A24<=\$B\$37:\$B\$49)*(\$B24>=\$B\$37:\$B\$49)*(\$B24>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A24<=\$B\$37:\$B\$49)*(\$B24>=\$B\$37:\$B\$49)*(\$B24<\$C\$37:\$C\$49)*((\$B24-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B24<\$B\$37:\$B\$49*\$D25)
D25=SUMPRODUCT((\$A25<=\$B\$37:\$B\$49)*(\$B25>=\$B\$37:\$B\$49)*(\$B25>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A25<=\$B\$37:\$B\$49)*(\$B25>=\$B\$37:\$B\$49)*(\$B25<\$C\$37:\$C\$49)*((\$B25-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B25<\$B\$37:\$B\$49*\$D26)
D26=SUMPRODUCT((\$A26<=\$B\$37:\$B\$49)*(\$B26>=\$B\$37:\$B\$49)*(\$B26>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A26<=\$B\$37:\$B\$49)*(\$B26>=\$B\$37:\$B\$49)*(\$B26<\$C\$37:\$C\$49)*((\$B26-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B26<\$B\$37:\$B\$49*\$D27)
D27=SUMPRODUCT((\$A27<=\$B\$37:\$B\$49)*(\$B27>=\$B\$37:\$B\$49)*(\$B27>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A27<=\$B\$37:\$B\$49)*(\$B27>=\$B\$37:\$B\$49)*(\$B27<\$C\$37:\$C\$49)*((\$B27-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B27<\$B\$37:\$B\$49*\$D28)
D28=SUMPRODUCT((\$A28<=\$B\$37:\$B\$49)*(\$B28>=\$B\$37:\$B\$49)*(\$B28>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A28<=\$B\$37:\$B\$49)*(\$B28>=\$B\$37:\$B\$49)*(\$B28<\$C\$37:\$C\$49)*((\$B28-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B28<\$B\$37:\$B\$49*\$D29)
D29=SUMPRODUCT((\$A29<=\$B\$37:\$B\$49)*(\$B29>=\$B\$37:\$B\$49)*(\$B29>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A29<=\$B\$37:\$B\$49)*(\$B29>=\$B\$37:\$B\$49)*(\$B29<\$C\$37:\$C\$49)*((\$B29-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B29<\$B\$37:\$B\$49*\$D30)
D30=SUMPRODUCT((\$A30<=\$B\$37:\$B\$49)*(\$B30>=\$B\$37:\$B\$49)*(\$B30>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A30<=\$B\$37:\$B\$49)*(\$B30>=\$B\$37:\$B\$49)*(\$B30<\$C\$37:\$C\$49)*((\$B30-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B30<\$B\$37:\$B\$49*\$D31)
D31=SUMPRODUCT((\$A31<=\$B\$37:\$B\$49)*(\$B31>=\$B\$37:\$B\$49)*(\$B31>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A31<=\$B\$37:\$B\$49)*(\$B31>=\$B\$37:\$B\$49)*(\$B31<\$C\$37:\$C\$49)*((\$B31-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))+SUMPRODUCT(\$B31<\$B\$37:\$B\$49*\$D32)
D32=SUMPRODUCT((\$A32<=\$B\$37:\$B\$49)*(\$B32>=\$B\$37:\$B\$49)*(\$B32>=\$C\$37:\$C\$49)*\$E\$37:\$E\$49)+SUMPRODUCT((\$A32<=\$B\$37:\$B\$49)*(\$B32>=\$B\$37:\$B\$49)*(\$B32<\$C\$37:\$C\$49)*((\$B32-\$B\$37:\$B\$49)*\$F\$37:\$F\$49+\$E\$37:\$E\$49/\$D\$37:\$D\$49))
D33=SUM(D21:D32)
E21=C21-BV21
E22=C22-BV22
E23=C23-BV23
E24=C24-BV24
E25=C25-BV25
E26=C26-BV26
E27=C27-BV27
E28=C28-BV28
E29=C29-BV29
E30=C30-BV30
E31=C31-BV31
E32=C32-BV32
E33=SUM(E21:E32)
E34=E33+BV33

Table 2
Excel Workbook
ABCDEF
36HB Payments ReferencePeriod BeginPeriod EndNumber Days in periodTotal Amount of PaymentDaily amount paid by HB
3701/01/201131/01/201131 310.00 10.00
3801/02/201128/02/201128 280.00 10.00
3901/03/201130/04/201161 610.00 10.00
4001/05/201130/05/201130 300.00 10.00
411 - -
421 - -
431 - -
441 - -
451 - -
461 - -
471 - -
481 - -
491 - -
Sheet1
Excel 2007
Cell Formulas
RangeFormula
D37=C37-B37+1
D38=C38-B38+1
D39=C39-B39+1
D40=C40-B40+1
D41=C41-B41+1
D42=C42-B42+1
D43=C43-B43+1
D44=C44-B44+1
D45=C45-B45+1
D46=C46-B46+1
D47=C47-B47+1
D48=C48-B48+1
D49=C49-B49+1
F37=IF(E37=0,0,E37/D37)
F38=IF(E38=0,0,E38/D38)
F39=IF(E39=0,0,E39/D39)
F40=IF(E40=0,0,E40/D40)
F41=IF(E41=0,0,E41/D41)
F42=IF(E42=0,0,E42/D42)
F43=IF(E43=0,0,E43/D43)
F44=IF(E44=0,0,E44/D44)
F45=IF(E45=0,0,E45/D45)
F46=IF(E46=0,0,E46/D46)
F47=IF(E47=0,0,E47/D47)
F48=IF(E48=0,0,E48/D48)
F49=IF(E49=0,0,E49/D49)

thankyou alpha for showing my problem as they should be displayed the D15 cell quoted in cell A21 is just a date which then defines the date for all other date cells in table 1 (I should have mentioned this also) apolgies

Last edited:
In Table 2, are the Period Begin and Period End dates always the beginning-of-month dates and the end-of-month dates even if they span more than one month?

In Table 2, are the Period Begin and Period End dates always the beginning-of-month dates and the end-of-month dates even if they span more than one month?

The dates in table one are determined automatically from the date in Cell D15 (this makes them all monthly an example being the date in cell D15 being 21/01/2011 and so all the dates in table one will begin 21/mm/yyyy - and end 20/mm/yyyy and so on etc)

Table two's date ranges are input manually by me and can be any date to any date. litterally only 1 day to an entire year (never more than a year though because each sheet will only run a max of a year) This is why i need the payments in table two to split themselves up and correspond with the dates in table 1.

Thanks for your help on this So in your example data, all the Table 1 calendar month periods fall within only one period in Table 2. If that were always the case, then this would be a lot easier.

But if I understand you correctly, it is possible that a Table 1 calendar month period could span more than one period in Table 2. And you would want to sum each daily amount from Table 2 for each day that the period from Table 1 covers. Is that correct?

I'm just trying to understand all the various scenarios that may not be illustrated in the example data.

So in your example data, all the Table 1 calendar month periods fall within only one period in Table 2. If that were always the case, then this would be a lot easier.

But if I understand you correctly, it is possible that a Table 1 calendar month period could span more than one period in Table 2. And you would want to sum each daily amount from Table 2 for each day that the period from Table 1 covers. Is that correct?

I'm just trying to understand all the various scenarios that may not be illustrated in the example data.

hi again AlphaFrog, yes i think you have the gist of it, to cut it right back to its basics, table one works in PCM (per calendar month) and any payments made toward the rent of each PCM need to be worked out so they correspond.

Table two payments are made by housing benefit and can cover any date range (so any number of days) because Housing Benefit dont work to the PCM system which means that payments they make nearly always have to be split up into the relevant PCM payments.

Example: We get given a sheet from Housing benefit saying the payment for your tenant Mrs Smith is £500 and covers the dates from (23/04/2011) to (26/08/2011) which is 126 days. Now if Mrs smiths rent payment date with us is 1st of every month - this payment made by Housing Benefit is then relevant for the PCM months in table one of

01/04/2011 - 31/04/2011
01/05/2011 - 30/05/2011
01/06/2011 - 30/06/2011
01/07/2011 - 31/07/2011
01/08/2011 - 31/08/2011

and the payment of £500 from table two needs to be split so each day that the payment covers is accounted for

£500/ 126 days = £3.97 per day.

there for the amount of £3.97 per day needs to put toward the PCM amount in table one for every day that the payments are relevant eg because the payment of £500 is only relevant from 23/04/2011 it is only 8 days * £3.97 = £31.75. There for this amount would be placed in table one column D (relevant cell to aprils date range.)

then continuing from there
there are 30 days that the payment covers in may so 30*3.97= 119.05 and this amount would get placed in table one column D (Cell relevant to mays date range) and so on for the rest of the dates relevant for the payment

Does this help or confuse you even more?

There may be someone more clever that could devise a formula for you. The best I could do given the various scenarios you described was to make a custom User Defined Function (UDF) called PCMTotal.

Syntax:
PCMTotal(Start_Date, End_Date, Periods_range, Payments_range)

Place this code in a standard VBA module

Code:
``````Function PCMTotal(StartDate, EndDate, rPeriods As Range, rPayments As Range)

Dim vDays(), vPeriods, vPayments, i&, j&, sDaily!

If StartDate And EndDate Then

vPeriods = rPeriods
vPayments = rPayments

ReDim vDays(WorksheetFunction.Min(rPeriods) To WorksheetFunction.Max(rPeriods))

For i = 1 To UBound(vPeriods, 1)
If (vPeriods(i, 1) * vPeriods(i, 2) * vPayments(i, 1)) Then
sDaily = vPayments(i, 1) / (vPeriods(i, 2) - vPeriods(i, 1) + 1)
For j = vPeriods(i, 1) To vPeriods(i, 2)
vDays(j) = sDaily
Next j
End If
Next i

For i = Application.Max(StartDate, LBound(vDays)) To Application.Min(EndDate, UBound(vDays))
PCMTotal = PCMTotal + vDays(i)
Next i

End If

End Function``````

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">20</td><td style="font-weight: bold;text-align: center;background-color: #FFFF99;;">PCM START DATE (DUE)</td><td style="font-weight: bold;text-align: center;background-color: #FFFF99;;">PCM END DATE FOR CALANDAR MONTH</td><td style="font-weight: bold;text-align: center;background-color: #CCCCFF;;">Rent PCM Due</td><td style="font-weight: bold;text-align: center;background-color: #FFFFCC;;">HB Payment</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">01/01/2011</td><td style="text-align: center;;">31/01/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£310.00</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">01/02/2011</td><td style="text-align: center;;">28/02/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£280.00</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">01/03/2011</td><td style="text-align: center;;">31/03/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£310.00</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">01/04/2011</td><td style="text-align: center;;">30/04/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£300.00</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">01/05/2011</td><td style="text-align: center;;">31/05/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£300.00</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">01/06/2011</td><td style="text-align: center;;">30/06/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£1,050.00</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">01/07/2011</td><td style="text-align: center;;">31/07/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£350.00</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">01/08/2011</td><td style="text-align: center;;">31/08/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£0.00</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;;">01/09/2011</td><td style="text-align: center;;">30/09/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£0.00</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;">01/10/2011</td><td style="text-align: center;;">31/10/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£0.00</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">01/11/2011</td><td style="text-align: center;;">30/11/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£0.00</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">01/12/2011</td><td style="text-align: center;;">31/12/2011</td><td style="text-align: center;;">0</td><td style="text-align: center;;">£0.00</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D21</th><td style="text-align:left">=PCMTotal(<font color="Blue">A21,B21,\$B\$37:\$C\$47,\$E\$37:\$E\$47</font>)</td></tr></tbody></table></td></tr></table><br />

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">36</td><td style="font-weight: bold;text-align: center;background-color: #CCFFCC;;">HB Payments Reference</td><td style="font-weight: bold;text-align: center;background-color: #CCFFCC;;">Period Begin</td><td style="font-weight: bold;text-align: center;background-color: #CCFFCC;;">Period End</td><td style="font-weight: bold;text-align: center;background-color: #CCFFCC;;">Number Days in period</td><td style="font-weight: bold;text-align: center;background-color: #CCFFCC;;">Total Amount of Payment</td><td style="font-weight: bold;text-align: center;background-color: #CCFFCC;;">Daily amount paid by HB</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: center;;"></td><td style="text-align: center;;">01/01/2011</td><td style="text-align: center;;">31/01/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">£310.00</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: center;;"></td><td style="text-align: center;;">01/02/2011</td><td style="text-align: center;;">28/02/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">£280.00</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: center;;"></td><td style="text-align: center;;">01/03/2011</td><td style="text-align: center;;">30/04/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">£610.00</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: center;;"></td><td style="text-align: center;;">01/05/2011</td><td style="text-align: center;;">30/05/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">£300.00</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: center;;"></td><td style="text-align: center;;">01/06/2011</td><td style="text-align: center;;">15/06/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">£700.00</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: center;;"></td><td style="text-align: center;;">16/06/2011</td><td style="text-align: center;;">15/07/2011</td><td style="text-align: center;;"></td><td style="text-align: center;;">£700.00</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table>

You don't need to calculate the Number Days in period or the Daily amount paid by HB in Table 2 unless you want.

Replies
0
Views
192
Replies
5
Views
99
Replies
4
Views
266
Replies
3
Views
217
Replies
3
Views
151

### Forum statistics

1,206,942
Messages
6,075,772
Members
446,154
Latest member
Dirk46 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back