HOW TO CALCULATE SUM OF OUTSTANDING BALANCE AS OF DATE

Copper Contributor

HOW TO CALCULATE SUM OF OUTSTANDING BALANCE AS OF DATE

Hi,

I have to find the sum of balance contract amount, based on the criteria like contract maturity date not exceed the start date as a as of date.

I'm not able to arrive through the sumif/ifs condition.

Can you suggest me way to arrive, as the sample that i attached

2 Replies

Re: HOW TO CALCULATE SUM OF OUTSTANDING BALANCE AS OF DATE

I am not sure whether the date intervals you specify should be inclusive or exclusive of end dates, but putting that aside you could have a formula

``````= SUM(
)
)``````

To get an array of results you would need 365 and to introduce Lambda functions and MAP.

Re: HOW TO CALCULATE SUM OF OUTSTANDING BALANCE AS OF DATE

With 365.

``````Worksheet formula
= BYROW(AsOfDate, OutstandingBalanceλ(startDate,maturityDate,amount))

where OutstandingBalanceλ
= LAMBDA(start,maturity,amt,
LAMBDA(date,
LET(
balance, amt *
IF(
date < start,
1,