anoble
New Member
- Joined
- Oct 7, 2013
- Messages
- 16
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
<tbody>
</tbody>
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>