4 weekly/monthly sums

[dlrollings83]

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

 

[jorismoerings]

Hi,

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

 

[dlrollings83]

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>

 

[jorismoerings]

Hi,

take a look if this is working for you:

Excel 2016 (Windows) 64 bit
	A	F	G	H	I	J	K	BB	BC	BD	BE
1	Officer	2-5-2016	9-5-2016	16-5-2016	23-5-2016	30-5-2016	6-6-2016
2	Arrears	Week 05	Week 06	Week 07	Week 08	Week 09	Week 10	Weekly Change	Yearly Change	4 Weekly Change	Monthly Change
3	IO 1	110,533	93,883	109,735	120,082	110,000	97,885	-12,115	-22,203	4,002	-12,648
4								-12,115	22,203	4,002	-12,648
Sheet1

Cell Formulas
Range	Formula
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))