VAT Payments Conditional Sum with MOD function--HELP can't figure this out

[cafeaulait]

Hi all

Month 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3
P&L VAT charge 3 -21 -50 -25 -60 -2 -78 -10 -36 -44 -99 -6
Quarterly Pmt -68 -87 -124 -149

I need to calculate VAT payments every 3 months based on 2 or 1 month(s) time lag--(this is variable) AFTER the end of the quarter.(quarters are 1-3,4-6,7-9,10-12).

I need a formula which I can enter along the entire Quarterly Pmt row, that is uniform rather than doing manual sums as I have at present. I got close with =IF(MOD(D15,3)=$E$12,SUMIFS($B$6:$G$6,$B$5:$G$5,"<4"),0) in period 5 but it doesn't work if you extend it along the whole year.

1 month's time lag will be--- month 4 quarterly payment needs to sum months 1-3, month 7 needs to sum 4-6, month 10 needs to sum 7-9, month 1 needs to sum 10-12.

2 months' time lag will be--- month 5 quarterly payment needs to sum months 1-3, month 8 needs to sum 4-6, month 11 needs to sum 7-9, month 2 needs to sum 10-12.

All help much appreciated.

Genevieve

 

[BiocideJ]

This should do it... Not exactly how you want to handle the first month where results are not available (e.g. Month 1 does not have preceeding data so it cannot be calculated) so I added an IFERROR which will set the resulting cell to blank if there is no data (#REF error)

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Lag Time	1
2
3
4
5	Month	1	2	3	4	5	6	7	8	9	10	11	12	1	2	3
6	P&L VAT charge	3	-21	-50	-25	-60	-2	-78	-10	-36	-44	-99	-6
7	Qtrly Pymt				-68			-87			-124			-149

<TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula B7 =IFERROR(IF(MOD(B5,3)-$B$1=0,SUM(OFFSET(B6,0,-2-$B$1,1,3)),""),"") <TBODY> </TBODY>

<TBODY>
</TBODY>

You should be able to drag the cell in B7 to the right and get the calculations for each cell.
EDIT: Also, Lag time cannot be >2 as this will cause no cells to calculate. If this is a problem let me know and I can reformulate.