# Solved Calculating a field based on 5 other fields and 2 rules

September 20, 2018 at 04:23:07
Specs: Windows 7
 I want to be able to mathematically calculate the loan amount using the following fields and rules: Fields: SalesPrice (named range)Liens (named range)PropertyType (named range)LTV (named range) – formula used in that cell =LoanAmount/SalesPrice CLTV (named range) – formula used in that cell =(LoanAmount-Liens)/SalesPrice), Rules: If PropertyType=”Condo” Max LTV=95% otherwise 97%Max CLTV for everything is 105%.I need to calculate the named range loanamount based on the 2 rules, the issue is that the liens can change based on user input and I am having trouble figuring out the formula or the math to use to calculate the loanamount field while still adhering to the 2 rules. I was using this long IF statement because there was only 2 lien size possibilities at the time but now it can be any number. The loanprogram criteria in the formula was what was determining the amount of the lien but I don’t needthat criteria if I can figure out how to calculate the loan amount with any amount in the cell named liens. The SalesPrice criteria is there because for some reasons those values where breaking points when calculating based on the amount in the liens named range, which I also may not need if I can figure out the correct formula or VBA code to use.I feel like this should be easy but cannot figure it out and don’t know if anyone will understand my problem or even if this should be a formula or something I should do in VBA.This is the logic I need to follow: loan amount = (sales price X 105%) minus liensbut if PropertyType is condo the loan amount must be adjusted to not exceed 95% of the SalesPrice and also must be adjusted so that the CLTV (combined loan to value which is (loan amount plus liens) divided by SalesPrice)) to not exceed 105% of the SalesPriceif PropertyType is not a condo the loan amount must be adjusted to not exceed 97% of the SalesPrice minus the liens and also must be adjusted so that the CLTV (combined loan to value which is (loan amount plus liens) divided by SalesPrice)) to not exceed 105% of the SalesPrice```=IF(AND(PropertyType="Condo",LoanProgram="HFA Bond Miami",SalesPrice<150000),((SalesPrice*105%)+Liens), IF(AND(PropertyType="Condo",LoanProgram="HFA Bond Miami",SalesPrice>150000),(SalesPrice*95%), IF(AND(PropertyType="SFR",LoanProgram="HFA Bond Miami",SalesPrice<187500),((SalesPrice*105%)+Liens), IF(AND(PropertyType="Townhouse",LoanProgram="HFA Bond Miami",SalesPrice<187500),((SalesPrice*105%)+Liens), IF(AND(PropertyType="Condo",LoanProgram="HFA Bond Broward",SalesPrice<93700),((SalesPrice*105%)+Liens), IF(AND(PropertyType="Condo",LoanProgram="HFA Bond Broward",SalesPrice>93700),(SalesPrice*95%), IF(AND(PropertyType="SFR",LoanProgram="HFA Bond Broward",SalesPrice<93700),((SalesPrice*105%)+Liens), IF(AND(PropertyType="Townhouse",LoanProgram="HFA Bond Broward",SalesPrice<93700), ((SalesPrice*105%)+Liens),(SalesPrice*97%)))))))))```message edited by mecerrato

See More: Calculating a field based on 5 other fields and 2 rules

September 20, 2018 at 17:21:42
 Just out of curiosity, why are you entering the Lien amounts as negative numbers?Just as easy to subtract in the formula as to add. ((SalesPrice*105%) - Liens)Does your IF() formula work for you?Does it do the correct calculations?MIKEhttp://www.skeptic.com/

#1
September 20, 2018 at 04:40:15
 Please edit your formula by using carriage returns so that it fits within the reading pane. As posted, we have to constantly scroll left and right to read your text because of the length of the formula. The harder you make for us to read your posts, the less likely it is that you will get help.

#2
September 20, 2018 at 06:41:15
 No problem, done, thanks for reviewing :-)

#3
September 20, 2018 at 15:10:37
 A bit confused, you write that the formula is:loan amount = (sales price X 105%) minus liensBut in your IF() formula((SalesPrice*105%) + Liens)you have it as Plus LiensWhich is it?MIKEhttp://www.skeptic.com/

Related Solutions

#4
September 20, 2018 at 16:36:26
 That’s because the liens are entered as negative numbersmessage edited by mecerrato

#5
September 20, 2018 at 17:21:42
 Just out of curiosity, why are you entering the Lien amounts as negative numbers?Just as easy to subtract in the formula as to add. ((SalesPrice*105%) - Liens)Does your IF() formula work for you?Does it do the correct calculations?MIKEhttp://www.skeptic.com/

#6
September 20, 2018 at 19:36:52
 mmcconaghy I marked yours as the nest answer because although I cannot explain it but making the line negative just messes things up even though I have the math to correct it, it just doesn't flow logically so I went to an old spreadsheet that I had this formula working and saw that liens was also positive their. So I wrote this formula and it works like a charm:```=IF(AND(OR(PropertyType="SFR",PropertyType="Townhouse"),(LoanAmount/SalesPrice)>97%),SalesPrice*97%, IF(AND(PropertyType="Condo",(LoanAmount/SalesPrice)>95%),SalesPrice*95%,((SalesPrice*105%)-Liens)))```message edited by mecerrato