# 4 weekly/monthly sums

#### dlrollings83

##### New Member
Hi all,

I have some data regarding weekly debt levels but I have payers that pay weekly, fortnightly, 4 weekly and monthly. I want to monitor weekly, 4 weekly and monthly changes. I have the following for weekly and 4 weekly

= OFFSET(\$A3,0,(COUNT(\$A3:\$BA3)-COUNTIF(\$A3:\$BA3,0))-0)-OFFSET(\$A3,0,(COUNT(\$A3:\$BA3)-COUNTIF(\$A3:\$BA3,0))-1)
= OFFSET(\$A3,0,(COUNT(\$A3:\$BA3)-COUNTIF(\$A3:\$BA3,0))-0)-OFFSET(\$A3,0,(COUNT(\$A3:\$BA3)-COUNTIF(\$A3:\$BA3,0))-4)

These formulae are done so that when the debt is input the cell automatically inputs the increase/decrease....not sure how to do this for monthly though.

In Row 1 is the dates commencing 04/04/2016 and row Row is week 1 etc. I would like to know the difference whenever the date in row 1 is the first Monday of the month and compare that to the First Monday of the month previous.....hope that makes sense.

04/04/2016 to 02/05/2016 was 4 weeks but 02/05/2016 to 06/06/2016 is 5 weeks.

This has been driving me mad for a few months

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi,

Could you share a sample of the way your table looks like. Will help understanding your issue better.

Hi,

Could you share a sample of the way your table looks like. Will help understanding your issue better.

There are cells between week 10 and the difference hence the formula used. When I add something into the next week, Weekly, yearly and 4 weekly quote calculated the differences between the last cell with data and the previous, first and one 4 weeks previous....monthly I need to refer to the dates because as below, there are 5 weeks between payments. I need something in the Monthly change cell that basically says where the last cell has data and row A is the first Monday of the month, subtract the amount in the corresponding row where row A is the first Monday of the month previous

 Officer 02/05/2016 09/05/2016 16/05/2016 23/05/2016 30/05/2016 06/06/2016 Arrears Week 05 Week 06 Week 07 Week 08 Week 09 Week 10 Weekly Change Yearly Change 4 Weekly Change Monthly Change IO 1 110,533 93,883 109,735 120,082 110,000 97,885 -12115 -22203 4,002 -12,648

<tbody>
</tbody><colgroup><col><col span="5"><col><col><col><col><col></colgroup>

Hi,

take a look if this is working for you:

Excel 2016 (Windows) 64 bit
AFGHIJKBBBCBDBE
1Officer2-5-20169-5-201616-5-201623-5-201630-5-20166-6-2016
2ArrearsWeek 05Week 06Week 07Week 08Week 09Week 10Weekly ChangeYearly Change4 Weekly ChangeMonthly Change
3IO 1110,53393,883109,735120,082110,00097,885-12,115-22,2034,002-12,648
4-12,11522,2034,002-12,648
Sheet1
Cell Formulas
RangeFormula
BB4=INDEX(B3:BA3,MATCH(9.99999999999999E+307,B3:BA3))-INDEX(B3:BA3,MATCH(9.99999999999999E+307,B3:BA3)-1)
BC4=INDEX(B3:BA3,MATCH(9.99999999999999E+307,B3:BA3))-B3
BD4=INDEX(B3:BA3,MATCH(9.99999999999999E+307,B3:BA3))-INDEX(B3:BA3,MATCH(9.99999999999999E+307,B3:BA3)-4)
BE4=INDEX(\$B3:\$BA3,MATCH(9.99999999999999E+307,\$B3:\$BA3))-INDEX(\$B3:\$BA3,MATCH(EOMONTH(INDEX(\$B\$1:\$BA\$1,MATCH(9.99999999999999E+307,\$B3:\$BA3)),-2)+1+MOD(8-WEEKDAY(EOMONTH(INDEX(\$B\$1:\$BA\$1,MATCH(9.99999999999999E+307,\$B3:\$BA3)),-2)+1,2),7),\$B\$1:\$BA\$1,0))

Replies
6
Views
229
Replies
7
Views
226
Replies
5
Views
168
Replies
0
Views
109
Replies
1
Views
100

1,203,759
Messages
6,057,198
Members
444,913
Latest member
ILGSE

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