SOLVED

New Contributor

# New to IF/AND/OR formula

Hello. I can't seem to figure out how to get all the following conditions into one cell.
I will have a static amount in one cell, B2 (4.25%)

 Full Name First Name Last Name StreetAddress SecondaryAddress(apartment block) ZipCode City State Country ID Number Loan Amount Loan period in years Starting interest rate 4.25% **Cell B2** Effective interest rate Orla Lease Orla Lease 18531 Jast Wells Suite 782 55706 New Lempifort New Jersey **Cell H4** United States ID307191 407000 **Cell K4 30 **Cell L4** **Cell M4**

Here are the conditions I'm trying to get the cell (M4) to calculate

• If the loan amount is over \$400,000 subtract 1.00 percentage points (meaning that the Effective
The interest rate would be 3.25%), unless the period is 10 or fewer years
• If the loan amount is equal to or under \$400,000 and over \$300,000 subtract 0.75 percentage
points
• If the loan amount is equal to or under \$300,000 and over \$175,000 and 20 or fewer years
subtract 0.50 percentage points
• If the loan amount is equal to or under \$100,000 add 0.25 percentage points
• Lastly, in addition to any adjustments above, if the borrower lives in the state of New Jersey add
0.50 percentage points

Any help would be greatly appreciated. Using Microsoft 365 Excel (version 2201)

2 Replies
best response confirmed by AngelEspinoza (New Contributor)
Solution

# Re: New to IF/AND/OR formula

try it with this monster:

=IF(K4>400000;B2-1%+IF(H4="New Jersey";0,5%;0);IF(K4>300000;B2-0,75%+IF(H4="New Jersey";0,5%;0);IF(AND(K4>175000;K4<=300000;L4<=20);B2-0,5%+IF(H4="New Jersey";0,5%;0);IF(K4<=100000;B2+0,25%+IF(H4="New Jersey";0,5%;0);"???"))))

Depending on your local settings, you might need the exchange the ; with ,

# Re: New to IF/AND/OR formula

Thank you. I did have to play around with the semicolons. Cheers!