What is wrong with my if and formula

Copper Contributor

I am trying to have it calculate a rate based off of the credit score which is in C and the term which is in K

 

=IF((AND((C2<=579),(K2<=36))),(E2-0.3),(IF((AND((C2<=579),(37<=K2,K2<=60))),E2,(IF((AND((C2<=579),(K2>60))),(E2+0.6),(IF((AND((580<=C2,C2<=619),(K2<=36))),(E2-0.3),(IF((AND((580<=C2,C2<=619),(37<=K2,K2<=60))),E2,(IF((AND((580<=C2,C2<=619),(K2>60))),(E2+0.4),(IF((AND((620<=C2,C2<=649),(K2<=36))),(E2-0.3),(IF((AND((620<=C2,C2<=649),(37<=K2,K2<=60))),E2,(IF((AND((620<=C2,C2<=649),(K2>60))),(E2+0.6),(IF((AND((650<=C2,C2<=699),(K2<=36))),(E2-0.3),(IF((AND((650<=C2,C2<=699),(37<=K2,K2<=60))),E2,(IF((AND((650<=C2,C2<=699),(61<=K2,K2<=72))),(E2+0.3),(IF((AND((650<=C2,C2<=699),(K2>72))),(E2+1),(IF((AND((700<=C2,C2<=749),(K2<36))),(E2-0.3),(IF((AND((700<=C2,C2<=749),(37<=K2,K2<=60))),E2,(IF((AND((700<=C2,C2<=749)*(61<=K2,K2<=72))),(C2+0.5),(IF((AND((700<=C2,C2<=749),(K2>72))),(E2+1),(IF((AND((C2>=750),(K2<36))),(E2-0.3),(IF((AND((C2>=750),(37<=K2,K2<=60))),E2,(IF((AND((C2>=750),(61<=K2,K2<=72))),(E2+0.5),(IF((AND((C2>=750),(K2>72))),(E2+1),"fawk")))))))))))))))))))))))))))))))))))))))))

2 Replies

@YayiChop 

 Initial formula

=IF((AND((C2<=579),(K2<=36))),(E2-0.3),
 (IF((AND((C2<=579),(37<=K2,K2<=60))),E2,
 (IF((AND((C2<=579),(K2>60))),(E2+0.6),
 (IF((AND((580<=C2,C2<=619),(K2<=36))),(E2-0.3),
 (IF((AND((580<=C2,C2<=619),(37<=K2,K2<=60))),E2,
 (IF((AND((580<=C2,C2<=619),(K2>60))),(E2+0.4),
 (IF((AND((620<=C2,C2<=649),(K2<=36))),(E2-0.3),
 (IF((AND((620<=C2,C2<=649),(37<=K2,K2<=60))),E2,
 (IF((AND((620<=C2,C2<=649),(K2>60))),(E2+0.6),
 (IF((AND((650<=C2,C2<=699),(K2<=36))),(E2-0.3),
 (IF((AND((650<=C2,C2<=699),(37<=K2,K2<=60))),E2,
 (IF((AND((650<=C2,C2<=699),(61<=K2,K2<=72))),(E2+0.3),
 (IF((AND((650<=C2,C2<=699),(K2>72))),(E2+1),
 (IF((AND((700<=C2,C2<=749),(K2<36))),(E2-0.3),
 (IF((AND((700<=C2,C2<=749),(37<=K2,K2<=60))),E2,
 (IF((AND((700<=C2,C2<=749)*(61<=K2,K2<=72))),(C2+0.5),
 (IF((AND((700<=C2,C2<=749),(K2>72))),(E2+1),
 (IF((AND((C2>=750),(K2<36))),(E2-0.3),
 (IF((AND((C2>=750),(37<=K2,K2<=60))),E2,
 (IF((AND((C2>=750),(61<=K2,K2<=72))),(E2+0.5),
 (IF((AND((C2>=750),(K2>72))),(E2+1),"fawk")))))))))))))))))))))))))))))))))))))))))

returns some result, but it's overcomplicated. At least you may remove extra perenties

=IF( AND(C2<=579,K2<=36),           E2-0.3,
 IF( AND(C2<=579,37<=K2,K2<=60),    E2,
 IF( AND(C2<=579,K2>60),            E2+0.6,
 IF( AND(580<=C2,C2<=619, K2<=36),  E2-0.3,
 IF( AND(580<=C2,C2<=619, 37<=K2,K2<=60),E2,
 IF( AND(580<=C2,C2<=619, K2>60),   E2+0.4,
 IF( AND(620<=C2,C2<=649, K2<=36),  E2-0.3,
 IF( AND(620<=C2,C2<=649, 37<=K2,K2<=60),E2,
 IF( AND(620<=C2,C2<=649, K2>60),   E2+0.6,
 IF( AND(650<=C2,C2<=699, K2<=36),  E2-0.3,
 IF( AND(650<=C2,C2<=699, 37<=K2,K2<=60),E2,
 IF( AND(650<=C2,C2<=699, 61<=K2,K2<=72), E2+0.3,
 IF( AND(650<=C2,C2<=699, K2>72),   E2+1,
 IF( AND(700<=C2,C2<=749, K2<36),   E2-0.3,
 IF( AND(700<=C2,C2<=749, 37<=K2,K2<=60),E2,
 IF( AND(700<=C2,C2<=749, 61<=K2,K2<=72), C2+0.5,
 IF( AND(700<=C2,C2<=749, K2>72),   E2+1,
 IF( AND(C2>=750,K2<36),            E2-0.3,
 IF( AND(C2>=750,37<=K2,K2<=60),    E2,
 IF( AND(C2>=750,61<=K2,K2<=72),    E2+0.5,
 IF( AND(C2>=750,K2>72),            E2+1,
     "fawk")))))))))))))))))))))

result will be the same.

I'm almost sure it could be simplified more, especially if use helper data range with parameters, but reverse engineering without business logic defined is bit time consuming.

@YayiChop 

 

It would be better (Less confusing + easier to maintain when there are changes), if you created two dim tables, one for credit score and one for term, which would greatly simplify your decision IF() formula as well and create a formula like i.e.:

IF(VLOOKUP( CreditScoreValue, CreditScoreTable, [ReturnValue],0), [DoAnotherEvaluation or DoThisCalculation][DoAnotherEvaluation or DoThisCalculation])