FUNCTION ASSISTANCE NEEDED

[DSTOMPS]

Hello,

I'm having trouble figuring out a function solution for my excel files.

I need the following: IF a percentage is greater than 75 in a column (percentage is calculated by dividing the numbers in one column with another), then increase the numbers in the column I'm dividing into the numbers in the other column so that the percentage IS at 75%.

Any assistance would be GREATLY appreciated.  If clarification is required, I'm happy to provide.

Thank you!

[#2]

@DSTOMPS 

Could you create a small sample workbook (without sensitive data) that illustrates the problem and gives an indication of the desired result? Either attach it to a reply, or if that is not possible, upload it to for example OneDrive, share it and post a link to the uploaded file in a reply.

[#3]

@HansVogelaar Sure, here it is- https://1drv.ms/x/s!Ap-kuUhty-KUgb8EriIBWgIqRw10vw?e=uHHefl

[#4]

@DSTOMPS 

I'll be interested in how @HansVogelaar solves this (In Excel, there are always multiple ways to get from A to B).

I think you need to make use of what we call "helper columns," partly for clarity on what you're changing and why, partly to preserve the record of the original numbers.

So I've attached a solution that does that. One helper column with an adjusted figure for the property value, another showing the newly revised percentages (leaving the unadjusted as they were).

[#5]

@DSTOMPS 

After a bit of thinking, I arrived at a slightly different method, but using helper columns just like @mathetes. Without the helper columns, you'd end up with circular references.

I first calculated the adjusted loan-to-value ratio, then calculated the adjusted property value from that.

This is neither better nor worse than @mathetes's method, just arriving at the same result in a different order.

VBA could be an alternative, but it would erase the steps taken to arrive at the new values.

[#6]

@mathetes Thanks I like the solution but how to I add helper columns and what function do I plug in for excel to calculate it automatically?  Keep in mind, I'll be dealing with lists of 1000+ with 25-30% of them needing this adjustment.  I couldn't possibly do that on my own without a function.  Any insight on how to implement that function?  Thanks again for getting back to me.

[#7]

Thanks I like the solution but similar to @mathetes' solution, how to I add helper columns and what function do I plug in for excel to calculate the adjusted Loan to value automatically?  Keep in mind, I'll be dealing with lists of 1000+ with 25-30% of them needing this adjustment.  I couldn't possibly do that on my own without a function.  Any insight on how to implement that function?  Thanks again for getting back to me.

[#8 · Accepted solution]

@DSTOMPS The workbooks attached by @mathetes and by me show where to insert a new column and which formulas to enter.

For my example:

• Insert an empty column in column G.
• Enter the text Adjusted property value in G1.
• Enter the text Adjusted Loan-to-value ratio in L1 (column L is now the first unused column).
• Enter the formula =MIN(K2,75%) in L2, then fill down to the last used row, for example by double-clicking the fill handle in the lower right corner of L2.
• Enter the formula =(D2+E2)/L2 in G2, then fill down to the last used row.
• For rows in which the Loan-to-value ratio was already less than or equal to 75%, the Adjusted property value will be the same as the original property value; it will only be different for rows with a Loan-to-value ratio above 75%.

[#9]

This is great. Thank you so much for your help!! Thanks to @mathetes as well.

[#10]

@HansVogelaar 

I appreciate the greater simplicity of the MIN function to modify those that are over 75%. Good job,