Reached max character limit for a formula

[saraCWU]

Hello Forum... I am hoping someone may be able to help me. Maybe I am making my formula more complex than it needs to be.

I have 4 situations for each "main choice" (Monthly, Single) - "Jumbo">240, "Jumbo" <=240, No Jumbo >240, and No Jumbo <=240. I then have different amounts based on Credit Scores.

'C' is my lookup sheet with all of my numbers on it.

Now this formula works but I need to repeat it 3 additional times with different parameters (97-95.01% is below) but then I need it also for (95-90.01%, 90-85.01%, and 85-80.01%) - when I combine all 4 together it is saying my formula exceeds the 8,100 (approx) character limit.

Any way around this?

=IF(AND(E17="Monthly PMI",E21>240,D16<>"Jumbo",D17='C'!E11,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F11,IF(AND(E17="Monthly PMI",E21>240,D16="Jumbo",D17='C'!E11,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F11+'C'!K11,IF(AND(E17="Monthly PMI",E21<=240,D16<>"Jumbo",D17='C'!E23,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F23,IF(AND(E17="Monthly PMI",E21<=240,D16="Jumbo",D17='C'!E23,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F23+'C'!K23,IF(AND(E17="Monthly PMI",E21>240,D16<>"Jumbo",D17='C'!E12,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F12,IF(AND(E17="Monthly PMI",E21>240,D16="Jumbo",D17='C'!E12,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F12+'C'!K12,IF(AND(E17="Monthly PMI",E21<=240,D16<>"Jumbo",D17='C'!E24,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F24,IF(AND(E17="Monthly PMI",E21<=240,D16="Jumbo",D17='C'!E24,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F24+'C'!K24,IF(AND(E17="Monthly PMI",E21>240,D16<>"Jumbo",D17='C'!E13,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F13,IF(AND(E17="Monthly PMI",E21>240,D16="Jumbo",D17='C'!E13,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F13+'C'!K13,IF(AND(E17="Monthly PMI",E21<=240,D16<>"Jumbo",D17='C'!E25,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F25,IF(AND(E17="Monthly PMI",E21<=240,D16="Jumbo",D17='C'!E25,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F25+'C'!K25,IF(AND(E17="Monthly PMI",E21>240,D16<>"Jumbo",D17='C'!E14,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F14,IF(AND(E17="Monthly PMI",E21>240,D16="Jumbo",D17='C'!E14,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F14+'C'!K14,IF(AND(E17="Monthly PMI",E21<=240,D16<>"Jumbo",D17='C'!E26,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F26,IF(AND(E17="Monthly PMI",E21<=240,D16="Jumbo",D17='C'!E26,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F26+'C'!K26,IF(AND(E17="Monthly PMI",E21>240,D16<>"Jumbo",D17='C'!E15,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F15,IF(AND(E17="Monthly PMI",E21>240,D16="Jumbo",D17='C'!E15,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F15+'C'!K15,IF(AND(E17="Monthly PMI",E21<=240,D16<>"Jumbo",D17='C'!E27,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F27,IF(AND(E17="Monthly PMI",E21<=240,D16="Jumbo",D17='C'!E27,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F27+'C'!K27,IF(AND(E17="Monthly PMI",E21>240,D16<>"Jumbo",D17='C'!E16,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F16,IF(AND(E17="Monthly PMI",E21>240,D16="Jumbo",D17='C'!E16,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F16+'C'!K16,IF(AND(E17="Monthly PMI",E21<=240,D16<>"Jumbo",D17='C'!E28,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F28,IF(AND(E17="Monthly PMI",E21<=240,D16="Jumbo",D17='C'!E28,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F28+'C'!K28,IF(AND(E17="Monthly PMI",E21>240,D16<>"Jumbo",D17='C'!E17,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F17,IF(AND(E17="Monthly PMI",E21>240,D16="Jumbo",D17='C'!E17,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F17+'C'!K17,IF(AND(E17="Monthly PMI",E21<=240,D16<>"Jumbo",D17='C'!E29,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F29,IF(AND(E17="Monthly PMI",E21<=240,D16="Jumbo",D17='C'!E29,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F29+'C'!K29,IF(AND(E17="Monthly PMI",E21>240,D16<>"Jumbo",D17='C'!E18,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F18,IF(AND(E17="Monthly PMI",E21>240,D16="Jumbo",D17='C'!E18,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F18+'C'!K18,IF(AND(E17="Monthly PMI",E21<=240,D16<>"Jumbo",D17='C'!E30,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F30,IF(AND(E17="Monthly PMI",E21<=240,D16="Jumbo",D17='C'!E30,'C'!F1>=95.01%,'C'!F1<=97%),'C'!F30+'C'!K30))))))))))))))))))))))))))))))))

 

[AliGW]

You need to set up a lookup table and then use the VLOOKUP function.

 

[saraCWU]

Thank you, but I don't believe I can do what needs to be done with a vlookup as there are way too many arguments.

 

[dave3009]

Hi saraCWU, welcome to the forum.

I think (nothing to test it on) your formula above can be rewritten like so

=IF(AND(E17="Monthly PMI",E21>240,'C'!F1>=95.01%,'C'!F1<=97%, D16<>"Jumbo"), SUMIF('C'!E11:E30, D17,'C'!F11:F30), IF(AND(E17="Monthly PMI",E21>240,'C'!F1>=95.01%,'C'!F1<=97%, D16="Jumbo"), SUMIF('C'!E11:E30, D17,'C'!F11:F30)+SUMIF('C'!E11:E30, D17,'C'!K11:K30)))

Which should give you a little more space.

HTH

Dave