If statement works until it gets to row 47, what am I doing wrong.

Anonymous
2025-03-06T00:13:03+00:00

I created a financial loan agreement spread sheet. It works well until the loan is nearing completion. It appears if at line 47 the formula no longer works It add several lines to get the loan to zero$ I have also attached a link to the actual spreadsheet so you can see the formula and what happens when it gets to Row 47. https://limewire.com/d/913656b1-f497-4e19-b9ba-d7cd33c1abb3#zwfc8Oai3tDjZVz0m4FpEq-Vv58duqD7Ojxg94jfyHY Thanks for any help or suggestions on how to fix this.

Tony L

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
riny 21,520 Reputation points Volunteer Moderator
2025-03-06T13:00:47+00:00

Change the formula in I9 to:

=MIN(K8, $B$9-H9)

Then in G9 use this:

=SUM(H9:I9)

Copy both formulas all the way down and it works as you intend it to.

Was this answer helpful?

2 people found this answer helpful.
0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-03-06T15:35:31+00:00

    Riny_Van_Eekelen,

    Thank you very much, It worked perfectly.

    Best regards,

    Tony

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2025-03-06T12:01:54+00:00

    Hi Isabella,

    Thank you for your quick response.

    I have recreated it the spreadsheet with no extra payments, It still comes up with an error when I get to the last payment. I think it needs a false statement. If the amount is less than the actual payment. =IF($B$9

    Thanks,

    Tony

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-03-06T09:16:37+00:00

    Dear Titanium5er，

    Thank you for sharing this issue with the Microsoft community.

    I understand that when you create a formula in Excel, it no longer works after 47 rows, I understand your frustration and will try to help you troubleshoot.

    I tried to open the link you shared, which does have some errors in the formula after 47 rows, not displaying the corresponding values correctly. But I tried testing the formula in other cells and this seems to be working:

     

    I would like to confirm some information with you in order to get a better picture of the status of the problem:

    • Do you have any other formatting or rules set in the area where the formula is used?
    • Can you recreate an Excel file and apply the formula? Troubleshooting to see if the current Excel file is corrupt.

    I think the formulas you have entered in the document are correct and I suggest you try creating a new document to check.

    I hope this gives you some advice on troubleshooting.

    If you have any progress or other questions, feel free to contact us in the community.

    Best regards

    Isabella-MSFT | Microsoft Community Support Specialist

    Was this answer helpful?

    0 comments