So long story short, I have no idea what I am doing in VBA. Trying to learn. I am creating a excel sheet for clients to show them different estimates of a mortgage. I created drop-downs to select different loan programs, amortizations, loan-to-values, types of mortgage insurance, etc. I basically don't want to have to manually calculate any fields, especially mortgage insurance.
I want excel to return the correct figures based off the selections that I choose from the drop-down list.
I have created If functions that will calculate the proper mortgage insurance premiums, but unfortunately, it is too long. I get the error of "The specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format." I am using Excel 2010, so I thought I had 64 "options". Apparently not.
I would like to enter these formulas into VBA:
=IF(AND(E12="Maryland Mortgage Program",E14="Monthly",E15=97%),((E17*0.37%)/12),
IF(AND(E12="Maryland Mortgage Program",E14="Monthly",E15=95%),((E17*0.32%)/12),
IF(AND(E12="Maryland Mortgage Program",E14="Monthly",E15=90%),((E17*0.23%)/12),
IF(AND(E12="Maryland Mortgage Program",E14="Monthly",E15=85%),((E17*0.18%)/12),
IF(AND(E12="Maryland Mortgage Program",E14="Monthly",E15=80%),0,
IF(AND(E12="Maryland Mortgage Program",E14="Monthly",E15=75%),0,0)))))
IF(AND(E12="Conforming",E14="Monthly",E15=97%),((E17*0.55%)/12),
IF(AND(E12="Conforming",E14="Monthly",E15=95%),((E17*0.41%)/12),
IF(AND(E12="Conforming",E14="Monthly",E15=90%),((E17*0.3%)/12),
IF(AND(E12="Conforming",E14="Monthly",E15=85%),((E17*0.19%)/12),
IF(AND(E12="Conforming",E14="Monthly",E15=80%),0,
IF(AND(E12="Conforming",E14="Monthly",E15=75%),0,0))))))
IF(AND(E12="Maryland Mortgage Program",E14="Single Premium",E15=97%),E17*1.35%,
IF(AND(E12="Maryland Mortgage Program",E14="Single Premium",E15=95%),E17*1.15%,
IF(AND(E12="Maryland Mortgage Program",E14="Single Premium",E15=90%),E17*0.82%,
IF(AND(E12="Maryland Mortgage Program",E14="Single Premium",E15=85%),E17*0.52%,
IF(AND(E12="Maryland Mortgage Program",E14="Single Premium",E15=80%),0,
IF(AND(E12="Maryland Mortgage Program",E14="Single Premium",E15=75%),0,0))))))
IF(AND(E12="Conforming",E14="Single Premium",E15=97%),E17*2.1%,
IF(AND(E12="Conforming",E14="Single Premium",E15=95%),E17*1.7%,
IF(AND(E12="Conforming",E14="Single Premium",E15=90%),E17*1.2%,
IF(AND(E12="Conforming",E14="Single Premium",E15=85%),E17*0.62%,
IF(AND(E12="Conforming",E14="Single Premium",E15=80%),0,
IF(AND(E12="Conforming",E14="Single Premium",E15=75%),0,0))))))
I am fairly confused so if possible treat me like a 6 year old. Thanks!
Aucun commentaire:
Enregistrer un commentaire