I'm working on a google sheet that generates a monthly time series based on the following information:
- Start Date (C17)
- End Date (C18)
- Amount at the beginning of the period (C19)
- Deductions during the period (D19:L19)
My current setup looks like this:
I need two ArrayFormulas:
- One to populate the column's deducted amount (D19:L19) if the date (B21:B) is within the deduction date range (D17:17, D18:18)
- One to calculate the effective post-deduction amount at the end of each month.
I am using ArrayFormulas because I expect regular users to have a hard time auto-populating normal formulas after pasting particularly long rows of deduction details.
The time series table is intended to be generated by feeding data from another table - one that has the monthly starting amount and deduction details using the fields of Start Date, End Date and Amount. So far, I've been able to put together are the following:
Months in Range:
=datedif($C$17,$C$18,"M") - named range "ScheduleMonths"
Column of Months:
=ArrayFormula(edate(C17,row(B1:indirect("B"&ScheduleMonths))))
Monthly Total Remaining:
=ARRAYFORMULA(C21:indirect("C"&ScheduleMonths+20)-
D21:indirect("D"&ScheduleMonths+20)-E21:indirect("E"&ScheduleMonths+20)-
F21:indirect("F"&ScheduleMonths+20)-G21:indirect("G"&ScheduleMonths+20)-
H21:indirect("H"&ScheduleMonths+20)-I21:indirect("I"&ScheduleMonths+20)-
J21:indirect("J"&ScheduleMonths+20)-K21:indirect("K"&ScheduleMonths+20)-
L21:indirect("L"&ScheduleMonths+20))
This is the normal formula I set up for use in the table fields:
=if(AND(C$17<=$B21,$B21<=C$18),C$19,"")
I expect the resulting ArrayFormulas to populate all monthly deduction cells with the proper deduction amounts should the date (B21:B) fall within the deduction date range (D17:17, D18:18). So far, I have only achieved this with normal formula.
