Forum Discussion
FUNCTION ASSISTANCE NEEDED
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%.
HansVogelaar Sure, here it is- https://1drv.ms/x/s!Ap-kuUhty-KUgb8EriIBWgIqRw10vw?e=uHHefl
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.
- 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.
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%.
I appreciate the greater simplicity of the MIN function to modify those that are over 75%. Good job,
