SOLVED

New to IF/AND/OR formula

%3CLINGO-SUB%20id%3D%22lingo-sub-3115136%22%20slang%3D%22en-US%22%3ENew%20to%20IF%2FAND%2FOR%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3115136%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20I%20can't%20seem%20to%20figure%20out%20how%20to%20get%20all%20the%20following%20conditions%20into%20one%20cell.%3CBR%20%2F%3EI%20will%20have%20a%20static%20amount%20in%20one%20cell%2C%20B2%20(4.25%25)%3C%2FP%3E%3CTABLE%20width%3D%221866%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22160.682px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3EFull%20Name%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22160.639px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3EFirst%20Name%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22160.639px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3ELast%20Name%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22199.716px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BStreetAddress%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22253.892px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BSecondaryAddress(apartment%20block)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2287.9545px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BZipCode%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22140.795px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BCity%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22115.781px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BState%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2297.9119px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BCountry%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2282.6136px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BID%20Number%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2296.8892px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BLoan%20Amount%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22143.707px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BLoan%20period%20in%20years%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22163.778px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22160.682px%22%20height%3D%2230px%22%3E%3CFONT%20size%3D%222%22%3EStarting%20interest%20rate%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22160.639px%22%20height%3D%2230px%22%3E%3CFONT%20size%3D%222%22%3E4.25%25%20**Cell%20B2**%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22160.639px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22199.716px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22253.892px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287.9545px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22140.795px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115.781px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2297.9119px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2282.6136px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296.8892px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22143.707px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22163.778px%22%20height%3D%2230px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22160.682px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22160.639px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22160.639px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22199.716px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22253.892px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2287.9545px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22140.795px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22115.781px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2297.9119px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2282.6136px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2296.8892px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22143.707px%22%20height%3D%2257px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22163.778px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%3CSTRONG%3EEffective%20interest%20rate%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22160.682px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3EOrla%20Lease%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22160.639px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3EOrla%20%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22160.639px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3ELease%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22199.716px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B18531%20Jast%20Wells%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22253.892px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BSuite%20782%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2287.9545px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E55706%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22140.795px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BNew%20Lempifort%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22115.781px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BNew%20Jersey%20**Cell%20H4**%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2297.9119px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BUnited%20States%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2282.6136px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3BID307191%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2296.8892px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E407000%20**Cell%20K4%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22143.707px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E30%20**Cell%20L4**%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%22163.778px%22%20height%3D%2257px%22%3E%3CFONT%20size%3D%222%22%3E**Cell%20M4**%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EHere%20are%20the%20conditions%20I'm%20trying%20to%20get%20the%20cell%20(M4)%20to%20calculate%3C%2FP%3E%3CUL%3E%3CLI%3EIf%20the%20loan%20amount%20is%20over%20%24400%2C000%20subtract%201.00%20percentage%20points%20(meaning%20that%20the%20Effective%3CBR%20%2F%3EThe%20interest%20rate%20would%20be%203.25%25)%2C%20%3CEM%3Eunless%3C%2FEM%3E%20the%20period%20is%2010%20or%20fewer%20years%3C%2FLI%3E%3CLI%3EIf%20the%20loan%20amount%20is%20equal%20to%20or%20under%20%24400%2C000%20and%20over%20%24300%2C000%20subtract%200.75%20percentage%3CBR%20%2F%3Epoints%3C%2FLI%3E%3CLI%3EIf%20the%20loan%20amount%20is%20equal%20to%20or%20under%20%24300%2C000%20and%20over%20%24175%2C000%20and%2020%20or%20fewer%20years%3CBR%20%2F%3Esubtract%200.50%20percentage%20points%3C%2FLI%3E%3CLI%3EIf%20the%20loan%20amount%20is%20equal%20to%20or%20under%20%24100%2C000%20add%200.25%20percentage%20points%3C%2FLI%3E%3CLI%3ELastly%2C%20in%20addition%20to%20any%20adjustments%20above%2C%20if%20the%20borrower%20lives%20in%20the%20state%20of%20New%20Jersey%20add%3CBR%20%2F%3E0.50%20percentage%20points%3C%2FLI%3E%3C%2FUL%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%20Using%20Microsoft%20365%20Excel%20(version%202201)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3115136%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3115612%22%20slang%3D%22en-US%22%3ERe%3A%20New%20to%20IF%2FAND%2FOR%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3115612%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1296832%22%20target%3D%22_blank%22%3E%40AngelEspinoza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etry%20it%20with%20this%20monster%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(K4%26gt%3B400000%3BB2-1%25%2BIF(H4%3D%22New%20Jersey%22%3B0%2C5%25%3B0)%3BIF(K4%26gt%3B300000%3BB2-0%2C75%25%2BIF(H4%3D%22New%20Jersey%22%3B0%2C5%25%3B0)%3BIF(AND(K4%26gt%3B175000%3BK4%26lt%3B%3D300000%3BL4%26lt%3B%3D20)%3BB2-0%2C5%25%2BIF(H4%3D%22New%20Jersey%22%3B0%2C5%25%3B0)%3BIF(K4%26lt%3B%3D100000%3BB2%2B0%2C25%25%2BIF(H4%3D%22New%20Jersey%22%3B0%2C5%25%3B0)%3B%22%3F%3F%3F%22))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDepending%20on%20your%20local%20settings%2C%20you%20might%20need%20the%20exchange%20the%20%3B%20with%20%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3222740%22%20slang%3D%22en-US%22%3ERe%3A%20New%20to%20IF%2FAND%2FOR%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3222740%22%20slang%3D%22en-US%22%3EThank%20you.%20I%20did%20have%20to%20play%20around%20with%20the%20semicolons.%20Cheers!%3C%2FLINGO-BODY%3E
New Contributor

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 NameFirst NameLast Name StreetAddress SecondaryAddress(apartment block) ZipCode City State Country ID Number Loan Amount Loan period in years 
Starting interest rate4.25% **Cell B2**           
            Effective interest rate
Orla LeaseOrla Lease 18531 Jast Wells Suite 78255706 New Lempifort New Jersey **Cell H4** United States ID307191407000 **Cell K430 **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

Hi @AngelEspinoza 

 

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 ,

 

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