Spreadsheet: 782703d1654188520-opening-balance-to-take-from-previous-closing-balance-dynamic-array-circular-reference-dynamic-array-dependent
Sheet: Sheet1

Report Date | 2021-12-31 00:00:00
[NAME] (result in Dynamic Array) |  |  | 2022-02-28 00:00:00 | 2022-03-31 00:00:00 | 2022-04-30 00:00:00 | 2022-05-31 00:00:00 | 2022-06-30 00:00:00 | 2022-07-31 00:00:00 | 2022-08-31 00:00:00 | 2022-09-30 00:00:00 | 2022-10-31 00:00:00 | 2022-11-30 00:00:00 | 2022-12-31 00:00:00 |  | Expense Table |  |  |  |  | Unit Table
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ID | Type | Date | Amount |  | Date | Num of Unit
Total Expense (result in Dynamic Array) |  |  | 24 | 18 | 0 | 28 | 86 | 31 | 97 | 35 | 36 | 58 | 13 |  | Expense | a | 2022-01-15 00:00:00 | 74 |  | 2022-01-15 00:00:00 | 11
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | b | 2022-01-15 00:00:00 | 85 |  | 2022-02-15 00:00:00 | 10
# of Units (result in Dynamic Array) |  |  | 10 | 4 | 0 | 9 | 2 | 3 | 6 | 0 | 7 | 9 | 1 |  | Expense | a | 2022-02-15 00:00:00 | 24 |  | 2022-03-15 00:00:00 | 4
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | b | 2022-02-15 00:00:00 | 37 |  | 2042-04-15 00:00:00 | 9
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | a | 2022-03-15 00:00:00 | 18 |  | 2022-05-15 00:00:00 | 9
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | b | 2022-03-15 00:00:00 | 13 |  | 2022-06-15 00:00:00 | 2
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | a | 2042-04-15 00:00:00 | 16 |  | 2022-07-15 00:00:00 | 3
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | b | 2042-04-15 00:00:00 | 53 |  | 2022-08-15 00:00:00 | 6
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | a | 2022-05-15 00:00:00 | 28 |  | 2022-09-15 00:00:00 | 0
Open Balance |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | b | 2022-05-15 00:00:00 | 59 |  | 2022-10-15 00:00:00 | 7
Manual Additions |  |  |  |  |  |  |  |  |  | 100 | 500 | 1000 |  |  | Expense | a | 2022-06-15 00:00:00 | 86 |  | 2022-11-15 00:00:00 | 9
Draws                                                                   ($B$16*C5#) | 0.5 |  | 12 | 9 | 0 | 14 | 43 | 15.5 | 48.5 | 17.5 | 18 | 29 | 6.5 |  | Expense | b | 2022-06-15 00:00:00 | 87 |  | 2022-12-15 00:00:00 | 1
Cumulative Balance |  | Desired result to be [NAME] with formula logic as  C14#+C16#+  C15:N15* |  |  |  |  |  |  |  |  |  |  |  |  | Expense | a | 2022-07-15 00:00:00 | 31
Interest | 0.035 | Desired result to be [NAME]  with formula logic as  $B$18 x C17# |  |  |  |  |  |  |  |  |  |  |  |  | Expense | b | 2022-07-15 00:00:00 | 34
Payment | 0.6 | Desired result to be [NAME] with formula logic as follow (look at cell 24)+ |  |  |  |  |  |  |  |  |  |  |  |  | Expense | a | 2022-08-15 00:00:00 | 97
Closing Balance |  | Desired result to be [NAME] with formula logic as C17#+C18#+C19# |  |  |  |  |  |  | 100 | 500 | 1000 |  |  |  | Expense | b | 2022-08-15 00:00:00 | 38
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | a | 2022-09-15 00:00:00 | 35
*Note C20:N20 is not dynamic array, but user manual input numbers |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | b | 2022-09-15 00:00:00 | 44
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | a | 2022-10-15 00:00:00 | 36
LET (myCalc, $B$19 x C7# |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | b | 2022-10-15 00:00:00 | 99
CumulativeBalance_plus_Interest, C17#+C18# |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | a | 2022-11-15 00:00:00 | 58
IF( AND(C7#>0, myCalc <= CumulativeBalance_plus_Interest), myCalc, |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | b | 2022-11-15 00:00:00 | 55
IF( AND(C7#>0, myCalc > CumulativeBalance_plus_Interest), CumulativeBalance_plus_Interest, |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | a | 2022-12-15 00:00:00 | 13
Otherwise 0 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Expense | b | 2022-12-15 00:00:00 | 48
)
 | Dynamic Array of cell C14 to N16
 | Subtotal of the Dynamic Array Above
 | Putting it into 1 - given #[NAME]! Error