Rolling Sums & Parameters

[anoble]

Hello!

I am trying to create a list of rolling sums. I currently have a spreadsheet similar to the tables below. The highlighted cell is the portion of the formula I am trying to address. For each cell in the Adjusted Outstanding Contract Balance column I have:

=SUMIF($A$30:$A$41,$A$16,$B$30:$B$41)+IF(SUM($C$16:$C$27)=0,0,IF($C$16<>0,$B$6-$C$16,$B$6))

=SUMIF($A$30:$A$41,$A$17,$B$30:$B$41)+IF(SUM($C$17:$C$27)=0,0,IF($C$17<>0,$B$16-$C$17,$B$16))

=SUMIF($A$30:$A$41,$A$18,$B$30:$B$41)+IF(SUM($C$18:$C$27)=0,0,IF($C$18<>0,$B$17-$C$18,$B$17))

=SUMIF($A$30:$A$41,$A$19,$B$30:$B$41)+IF(SUM($C$19:$C$27)=0,0,IF($C$19<>0,$B$18-$C$19,$B$18))

=SUMIF($A$30:$A$41,$A$20,$B$30:$B$41)+IF(SUM($C$20:$C$27)=0,0,IF($C$20<>0,$B$19-$C$20,$B$19))

=SUMIF($A$30:$A$41,$A$21,$B$30:$B$41)+IF(SUM($C$21:$C$27)=0,0,IF($C$21<>0,$B$20-$C$21,$B$20))

=SUMIF($A$30:$A$41,$A$22,$B$30:$B$41)+IF(SUM($C$22:$C$27)=0,0,IF($C$22<>0,$B$21-$C$22,$B$21))

=SUMIF($A$30:$A$41,$A$23,$B$30:$B$41)+IF(SUM($C$23:$C$27)=0,0,IF($C$23<>0,$B$22-$C$23,$B$22))

=SUMIF($A$30:$A$41,$A$24,$B$30:$B$41)+IF(SUM($C$24:$C$27)=0,0,IF($C$24<>0,$B$23-$C$24,$B$23))

=SUMIF($A$30:$A$41,$A$25,$B$30:$B$41)+IF(SUM
($C$25:$C$27)=0,0,IF($C$25<>0,$B$24-$C$25,$B$24))

=SUMIF($A$30:$A$41,$A$26,$B$30:$B$41)+IF(SUM($C$26:$C$27)=0,0,IF($C$26<>0,$B$25-$C$26,$B$25))

=SUMIF($A$30:$A$41,$A$27,$B$30:$B$41)+IF($C$27=0,0,IF($C$27<>0,$B$26-$C$27,$B$26))

I think there is a blank cell (highlighted) because I am not adressing the Adjusted Outstanding Contract Balance column within the formula, but I am unsure. I am also unsure how to go about incorporating this parameter into the formulas above. Any assitance with this this problem would be greatly appreciated.

Thank you,
ANoble

	A	B	D	E	F
1 & 2	Burn Rate Analysis as of Date:	Period of Performance (POP)	POP Total (Months)
Begin	End
3	January 22,2016	July 29,2015	July 28,2016	12
4
5	Amount Obligated on Contract:	$ 2,420,673.60	Prior Fiscal Year Invoice Total:	$ 324,002.40
6	Amount Obligated on Contract Total:	$ 2,096,671.20	Invoice Month Total	2
7	Adjustments to Contract Total:	$ (1,000,000.00)	<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"> </o:lock></vath></v:stroke></v:shapetype> <tbody> </tbody>
8	Revised Contract Total:	$ 1,096,671.20
9	Actual Burn Rate Average	$ 708,000.80
10	Actual Burn Rate (+5%)	$ 743,400.84
11	Actual Burn Rate (-5%)	$ 672,600.76
12	Average Burn Rate	$ 91,389.27
13
14				CHART USE ONLY
15	Invoice Date	Adjusted Outstanding Contract Balance	Invoice Amount	Actual Burn Rate Average	Actual Burn Rate (+5%)	Actual Burn Rate (-5%)
16	15-Oct	$ 2,046,671.20	$ 50,000.00	$ -	$ -	$ -
17	15-Nov	$ 2,046,671.20	$ -	$ -	$ -	$ -
18	15-Dec	$ 2,046,671.20	$ -	$ -	$ -	$ -
19	16-Jan	$ 1,546,671.20	$ -	$ -	$ -	$ -
20	16-Feb	$ 1,046,671.20	$ 500,000.00	$ -	$ -	$ -
21	16-Mar	$ 996,671.20	$ 50,000.00	$ -	$ -	$ -
22	16-Apr	$ -	$ -	$ -	$ -	$ -
23	16-May	$ (500,000.00)	$ -	$ -	$ -	$ -
24	16-Jun	$ -	$ -	$ -	$ -	$ -
25	16-Jul	$ -	$ -	$ -	$ -	$ -
26	16-Aug	$ -	$ -	$ -	$ -	$ -
27	16-Sep	$ -	$ -	$ -	$ -	$ -
28
29	Adjustment Date	Adjustment to Contract Balance (+/-)	Comments
30	15-Oct	$ -
31	15-Nov	$ -
32	15-Dec	$ -
33	16-Jan	$ (500,000.00)
34	16-Feb	$ -
35	16-Mar	$ -
36	16-Apr	$ -
37	16-May	$ (500,000.00)
38	16-Jun	$ -
39	16-Jul	$ -
40	16-Aug	$ -
41	16-Sep	$ -
42
43	Prior Fiscal Year Invoice Comments
44	Comments	Date	Amount
45	Prior Fiscal Year Invoice	Aug-15	$ 162,001.20
46	Prior Fiscal Year Invoice	Sep-15	$ 162,001.20
47			$ -
48			$ -

<tbody>
</tbody>

 

[Andrew Poulsom]

In what range are those formulas?

 

[anoble]

The range is $A$16:$C$27 and $A$30:$B$41.

 

[Andrew Poulsom]

No, those are the ranges that the formulas are referencing. Where are the formulas?

 

[anoble]

=sumif($a$30:$a$41,$a$16,$b$30:$b$41)+if(sum($c$16:$c$27)=0,0,if($c$16<>0,$b$6-$c$16,$b$6))

=sumif($a$30:$a$41,$a$17,$b$30:$b$41)+if(sum($c$17:$c$27)=0,0,if($c$17<>0,$b$16-$c$17,$b$16))

=sumif($a$30:$a$41,$a$18,$b$30:$b$41)+if(sum($c$18:$c$27)=0,0,if($c$18<>0,$b$17-$c$18,$b$17))

=sumif($a$30:$a$41,$a$19,$b$30:$b$41)+if(sum($c$19:$c$27)=0,0,if($c$19<>0,$b$18-$c$19,$b$18))

=sumif($a$30:$a$41,$a$20,$b$30:$b$41)+if(sum($c$20:$c$27)=0,0,if($c$20<>0,$b$19-$c$20,$b$19))

=sumif($a$30:$a$41,$a$21,$b$30:$b$41)+if(sum($c$21:$c$27)=0,0,if($c$21<>0,$b$20-$c$21,$b$20))

=sumif($a$30:$a$41,$a$22,$b$30:$b$41)+if(sum($c$22:$c$27)=0,0,if($c$22<>0,$b$21-$c$22,$b$21))

=sumif($a$30:$a$41,$a$23,$b$30:$b$41)+if(sum($c$23:$c$27)=0,0,if($c$23<>0,$b$22-$c$23,$b$22))

=sumif($a$30:$a$41,$a$24,$b$30:$b$41)+if(sum($c$24:$c$27)=0,0,if($c$24<>0,$b$23-$c$24,$b$23))

=sumif($a$30:$a$41,$a$25,$b$30:$b$41)+if(sum
($c$25:$c$27)=0,0,if($c$25<>0,$b$24-$c$25,$b$24))

=sumif($a$30:$a$41,$a$26,$b$30:$b$41)+if(sum($c$26:$c$27)=0,0,if($c$26<>0,$b$25-$c$26,$b$25))

=sumif($a$30:$a$41,$a$27,$b$30:$b$41)+if($c$27=0,0,if($c$27<>0,$b$26-$c$27,$b$26))

 

[Andrew Poulsom]

I know what the formulas are. What cells are they in?

 

[anoble]

Oh, they are in the Adjusted Outstanding Contract Balance Column.

 

[Andrew Poulsom]

Maybe in B16 copied down:

=B$6+SUMIF(A$30:A$41,"<="&A16,B$30:B$41)-SUMIF(A$16:A$27,"<="&A16,C$16:C$27)

 

[anoble]

I tried =B$6+SUMIF(A$30:A$41,"<="&A16,B$30:B$41)-SUMIF(A$16:A$27,"<="&A16,C$16:C$27) in B16, but it started the amount at $1,600,000 dollars less than what the starting amount should be ($2,096,671.20).

 

[Andrew Poulsom]

I had to change the year in the dates that were in the next year:

Excel 2010
	A	B
15	Invoice Date	Adjusted Outstanding Contract Balance
16	15-Oct-2016	2,046,671.20
17	15-Nov-2016	2,046,671.20
18	15-Dec-2016	2,046,671.20
19	16-Jan-2017	1,546,671.20
20	16-Feb-2017	1,046,671.20
21	16-Mar-2017	996,671.20
22	16-Apr-2017	996,671.20
23	16-May-2017	496,671.20
24	16-Jun-2017	496,671.20
25	16-Jul-2017	496,671.20
26	16-Aug-2017	496,671.20
27	16-Sep-2017	496,671.20
28
29	Adjustment Date	Adjustment to Contract Balance (+/-)
30	15-Oct-2016	-
31	15-Nov-2016	-
32	15-Dec-2016	-
33	16-Jan-2017	-500,000.00
34	16-Feb-2017	-
35	16-Mar-2017	-
36	16-Apr-2017	-
37	16-May-2017	-500,000.00
38	16-Jun-2017	-
39	16-Jul-2017	-
40	16-Aug-2017	-
41	16-Sep-2017	-
Sheet1

Cell Formulas
Range	Formula
B16	=B$6+SUMIF(A$30:A$41,"<="&A16,B$30:B$41)-SUMIF(A$16:A$27,"<="&A16,C$16:C$27)