# What is wrong with my if and formula

Occasional Visitor

# 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((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

# Re: What is wrong with my if and formula

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.

# Re: What is wrong with my if and formula

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])