When an amount reaches a max amount then zero after that

[rajkenya1]

Hi Guys

I need help on a formula. In the attached spreadsheet i need a formula say starting from cell C6 which says if the amount in that cell reaches total loan amount of $8m then the next cell to cell d7 should be zero.

Please help

 

[rajkenya1]

I have tried a formula in the attached sheet but it doesnt work. If someone can please help. Thank you

 

[pecoflyer]

Cross post https://www.excelforum.com/excel-ge...-amount-then-zero-after-that.html#post5556137

 

[vletm]

rajkenya1
Please, reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

... and You should remember soon few basic steps which are for You too.

 

[Peter Bartholomew]

It also depends on the version of Excel that you are using. Assuming 365

= LET(
  accumulated, SUMIFS(drawdown, year, "<="&year),
  IF(accumulated<LoanAmount, accumulated, LoanAmount))

should work but, in future, so would

= SCAN(0,year,
    LAMBDA(acc,yr,
      MIN(acc+XLOOKUP(yr,year,drawdown), LoanAmount)
    )
  )

 

[rajkenya1]

Thank you so much

Although have never seen a formula like this.

Cheers for your help

It also depends on the version of Excel that you are using. Assuming 365

= LET(
  accumulated, SUMIFS(drawdown, year, "<="&year),
  IF(accumulated<LoanAmount, accumulated, LoanAmount))

should work but, in future, so would

= SCAN(0,year,
    LAMBDA(acc,yr,
      MIN(acc+XLOOKUP(yr,year,drawdown), LoanAmount)
    )
  )

 

[rajkenya1]

Thank you so much for your reply

Cross post https://www.excelforum.com/excel-ge...-amount-then-zero-after-that.html#post5556137

 

[rajkenya1]

Thank you so much for your reply and sharing your knowledge. Much appreciated.

rajkenya1
Please, reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

... and You should remember soon few basic steps which are for You too.

 

[pecoflyer]

Thank you so much for your reply and sharing your knowledge. Much appreciated.

Is that the equivalent of " I don't give a **** ***** cross posting rules?" ?
moderator: Maybe that OP do not understand that everywhere are rules.

 

[Peter Bartholomew]

Thank you so much

Although have never seen a formula like this.

Cheers for your help

Not surprising. I post the solutions I use and care little for 'standard' spreadsheet practice. A version that should work on legacy Excel is

= IF(
     SUMIFS(drawdown, year, "<="&year) < LoanAmount,
     SUMIFS(drawdown, year, "<="&year),
     LoanAmount
  )

By the way, do post the solution you go with to each of the other forums you have asked for help. That way members need not spend time trying to develop solutions that you no longer require. I know it requires additional effort, but there is no harm in staying on good terms with those that have tried to help.

 

[rajkenya1]

Thanks. I will share it here. Thank you

 

[rajkenya1]

I dont understand why you are using such language against genuine people like me.

Have you got something against me?

Is that the equivalent of " I don't give a **** ***** cross posting rules?" ?
moderator: Maybe that OP do not understand that everywhere are rules.