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
2
3
4
5	Month
6	P&L VAT charge
7	Qtrly Pymt

<TBODY>
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]7
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]11
[/TD]
[TD="align: right"]12
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]

[TD="align: right"]3
[/TD]
[TD="align: right"]-21
[/TD]
[TD="align: right"]-50
[/TD]
[TD="align: right"]-25
[/TD]
[TD="align: right"]-60
[/TD]
[TD="align: right"]-2
[/TD]
[TD="align: right"]-78
[/TD]
[TD="align: right"]-10
[/TD]
[TD="align: right"]-36
[/TD]
[TD="align: right"]-44
[/TD]
[TD="align: right"]-99
[/TD]
[TD="align: right"]-6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]-68
[/TD]

[TD="align: right"]-87
[/TD]

[TD="align: right"]-124
[/TD]

[TD="align: right"]-149
[/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas

[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]B7
[/TH]
[TD="align: left"]=IFERROR(IF(MOD(B5,3)-$B$1=0,SUM(OFFSET(B6,0,-2-$B$1,1,3)),""),"")
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

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.