• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Loan overdue and number of days overdue

Ufoo

Member
Hello again experts. I need your help. My problem is well illustrated in the attached excel sheet. Thanks in advance
 

Attachments

  • Loan overdue and days overdue.XLSX
    212.4 KB · Views: 19
Hello excel experts, I have come up with the following formula for solving the problem above. Entered at cell H6, and then dragged down. It works perfectly, but I do not like it because it is causing very slow calculation. Would you please bring up an advice/ alternative way of solving this problem? I thank you in advance. I hope someone will help me solve this.

=IF(AND(E6="repayment",TODAY()-INDEX($C$6:$C$1015,MAX(INDEX(($B$6:$B$1015&$D$6:$D$1015=C6&"loan")*MATCH(ROW($B$6:$B$1015),ROW($B$6:$B$1015)),,)))>90,INDEX($G$6:$G$1015,MAX(INDEX(($B$6:$B$1015&$D$6:$D$1015=C6&"repayment")*MATCH(ROW($B$6:$B$1015),ROW($B$6:$B$1015)),,)))>0),H6,"")
 
Hello excel experts, I have come up with the following formula for solving the problem above. Entered at cell H6, and then dragged down. It works perfectly, but I do not like it because it is causing very slow calculation. Would you please bring up an advice/ alternative way of solving this problem? I thank you in advance. I hope someone will help me solve this.

=IF(AND(E6="repayment",TODAY()-INDEX($C$6:$C$1015,MAX(INDEX(($B$6:$B$1015&$D$6:$D$1015=C6&"loan")*MATCH(ROW($B$6:$B$1015),ROW($B$6:$B$1015)),,)))>90,INDEX($G$6:$G$1015,MAX(INDEX(($B$6:$B$1015&$D$6:$D$1015=C6&"repayment")*MATCH(ROW($B$6:$B$1015),ROW($B$6:$B$1015)),,)))>0),H6,"")
Hello, after some work I have come up with this: =IF(AND(E6="repayment",TODAY()-MAX(INDEX((B6:B1015=B6)*(B6:B1015="loan")*C6:C1015,0))>90,G6>0),G6,"").
 
Back
Top