Gregg Wolin
Member
See attached. I'd like the formula in the outlined row to spill like the date row that it refers to so they are all dynamic.
Attachments
-
20.4 KB Views: 8
This is perfect!Please try
=F5*(1+G5)^INT(YEARFRAC(EOMONTH(+J4#,-1),C5))*(J4#>C5)*(J4#<=E5)
= SCAN(0, periodEnd#, LAMBDA(acc,p,
IF(AND(p>=Start,p<=End),IF(MONTH(p)=Month,IF(acc>0,1+Amount,1),1)*IF(acc<>0,acc,Rate),0)
))
= SCAN(0, periodEnd#, LAMBDA(acc,p,
IF(AND(p>=startDate,p<=endDate),
IF(MONTH(p)=escMonth,IF(acc>0,1+rate,1),1)*IF(acc<>0,acc,amount),
0)
))
Follow-Up Question:I have just noticed that the Amount and Rate annotation was switched so when they got used in the defined names the resulting formula was somewhat incomprehensible, despite giving the same results.
What is difficult with these array formulas is to generate results for additional leases without using relative referencing. The correct answer should be an array of arrays but modern Excel will not accept them in order to maintain compatibility with legacy Excel.Code:= SCAN(0, periodEnd#, LAMBDA(acc,p, IF(AND(p>=startDate,p<=endDate), IF(MONTH(p)=escMonth,IF(acc>0,1+rate,1),1)*IF(acc<>0,acc,amount), 0) ))
= SCAN(0, periodEnd#, LAMBDA(acc,p,
LET(
active?, AND(p>=startDate,p<=endDate),
escalate?, IF(active?, MOD(DATEDIF(startDate,p,"m"),escMonth)=0),
newAmt, IF(active?,
IF(acc=0, amount, IF(escalate?,acc*(1+rate), acc)),
0),
newAmt)
))