This can get complicated so I'll try to present a basic example of what is happening. A row within a chart will have both positive and negative amounts (J8 through S8 below). The positive amounts reported on this row will be used to offset/cancel out the negative amounts. This means that we can use the amount in cell L8 to offset a portion of the amount in cell J8. Once the amount is used, it cannot be used again. There is also the possibility that an amount can be left over. For example, the amount in cell L8, had it been more than the amount in J8, the remainder could be used against the next negative amount. To keep it as clean as possible, there with only be one positive amount and one negative amount in each row below row 8. This means, had the amount in cell L8 been used against J8, we would have had a positive 1,000,000 in J8 and a negative 1,000,000 in cell S8. This would show the amount coming from J8 and shifting over to L8.
| CBT-100U Workpapers 6-5TEST.xlsm | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| J | K | L | M | N | O | P | Q | R | S | |||
| 5 | ||||||||||||
| 6 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | ||
| 7 | ||||||||||||
| 8 | (33,688,598) | (29,587,046) | 1,000,000 | (28,027,007) | (15,752,937) | (28,579,386) | 34,000,000 | (50,355,149) | (43,146,535) | (40,980,658) | ||
| 9 | ||||||||||||
| 10 | ||||||||||||
| 11 | ||||||||||||
| 12 | ||||||||||||
| 13 | ||||||||||||
| 14 | ||||||||||||
| 15 | ||||||||||||
| 16 | ||||||||||||
| 17 | ||||||||||||
| 18 | ||||||||||||
| 19 | ||||||||||||
NOL-TEMPLATE | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| Q8:S8,M8:O8,J8:K8 | J8 | =-(IFERROR(INDEX('DATA 500U-P-22'!$G$12:$G$3000,MATCH(1,INDEX(('DATA 500U-P-22'!$C$12:$C$3000=$A$6)*(YEAR('DATA 500U-P-22'!$F$12:$F$2000)=J6),0),0),0),0)) |
