Forum Discussion
DSTOMPS
Mar 19, 2024Copper Contributor
FUNCTION ASSISTANCE NEEDED
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 n...
- Mar 20, 2024
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%.
DSTOMPS
Mar 20, 2024Copper Contributor
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.
HansVogelaar
Mar 20, 2024MVP
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%.
- mathetesMar 20, 2024Silver Contributor
I appreciate the greater simplicity of the MIN function to modify those that are over 75%. Good job,