Keeping percentages at or below 100

**JimDandy** · 10-21-2010, 04:18 PM

I have a spreadsheet that performs some financial mortgage calculations. This sheet has 2 sets of fields where you can enter a dollar amount and a corresponding percentage is calculated (using a constant value for calculations), OR you could enter the percentage and the corresponding dollar amount is calculated. The problem I now have is that it’s possible to enter two percentages, or dollar amounts, where the total of the 2 sets of numbers exceed 100% of the constant value.

How can I force the percentage (and therefore the dollar amount) of the other set of numbers to change if I enter a percentage (or dollar amount) that causes the total to exceed 100%. The change should cause the total percentage to be 100%.

The fields that I’m in need of fixing are the Down Payment and Loan Amount fields.

A sample worksheet is attached.

**teddy.montoya** · 10-21-2010, 05:53 PM

Jim,
Could you send a workbook that doesnt have links - just the formulas you are using?
Teddy

**jaslake** · 10-21-2010, 06:25 PM

Hi Jim

I've tested this on your basic data. See if the code works for you.

Please Login or Register  to view this content.

Let me know of issues.

John

**JimDandy** · 10-21-2010, 11:10 PM

This code is very close....it works when modifying the percentage values, but if I modify the dollar amount, it correctly sets the percentage for that line only, it does not recalculate the percentage/dollar amount for the other set of values.

**jaslake** · 10-22-2010, 12:09 AM

Hi Jim

You've got a lot of stuff going on here. It's getting late but I'll take one last shot before bed

Please Login or Register  to view this content.

Let me know of issues. I'll look at it again tomorrow.

John

**JimDandy** · 10-22-2010, 12:58 AM

I think you nailed it that time....many thanks!

I will point out that there is one correction I made from what I cut here. It appears to be a typo, quickly found and corrected. The 14th line has "If Not Is Numeric..." and should be "If Not IsNumeric...". An extra space was added, but otherwise, it seems to work fine once corrected

I'll look at it again tomorrow and mark it solved if I can't find anything wrong with it, but it appears you got it right. I do appreciate your help.