Forum Discussion

YayiChop's avatar
YayiChop
Copper Contributor
Oct 28, 2021

What is wrong with my if and formula

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((...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 28, 2021

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.

Resources