What is wrong with my if and formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2897385%22%20slang%3D%22en-US%22%3EWhat%20is%20wrong%20with%20my%20if%20and%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2897385%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20have%20it%20calculate%20a%20rate%20based%20off%20of%20the%20credit%20score%20which%20is%20in%20C%20and%20the%20term%20which%20is%20in%20K%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF((AND((C2%26lt%3B%3D579)%2C(K2%26lt%3B%3D36)))%2C(E2-0.3)%2C(IF((AND((C2%26lt%3B%3D579)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C(IF((AND((C2%26lt%3B%3D579)%2C(K2%26gt%3B60)))%2C(E2%2B0.6)%2C(IF((AND((580%26lt%3B%3DC2%2CC2%26lt%3B%3D619)%2C(K2%26lt%3B%3D36)))%2C(E2-0.3)%2C(IF((AND((580%26lt%3B%3DC2%2CC2%26lt%3B%3D619)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C(IF((AND((580%26lt%3B%3DC2%2CC2%26lt%3B%3D619)%2C(K2%26gt%3B60)))%2C(E2%2B0.4)%2C(IF((AND((620%26lt%3B%3DC2%2CC2%26lt%3B%3D649)%2C(K2%26lt%3B%3D36)))%2C(E2-0.3)%2C(IF((AND((620%26lt%3B%3DC2%2CC2%26lt%3B%3D649)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C(IF((AND((620%26lt%3B%3DC2%2CC2%26lt%3B%3D649)%2C(K2%26gt%3B60)))%2C(E2%2B0.6)%2C(IF((AND((650%26lt%3B%3DC2%2CC2%26lt%3B%3D699)%2C(K2%26lt%3B%3D36)))%2C(E2-0.3)%2C(IF((AND((650%26lt%3B%3DC2%2CC2%26lt%3B%3D699)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C(IF((AND((650%26lt%3B%3DC2%2CC2%26lt%3B%3D699)%2C(61%26lt%3B%3DK2%2CK2%26lt%3B%3D72)))%2C(E2%2B0.3)%2C(IF((AND((650%26lt%3B%3DC2%2CC2%26lt%3B%3D699)%2C(K2%26gt%3B72)))%2C(E2%2B1)%2C(IF((AND((700%26lt%3B%3DC2%2CC2%26lt%3B%3D749)%2C(K2%26lt%3B36)))%2C(E2-0.3)%2C(IF((AND((700%26lt%3B%3DC2%2CC2%26lt%3B%3D749)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C(IF((AND((700%26lt%3B%3DC2%2CC2%26lt%3B%3D749)*(61%26lt%3B%3DK2%2CK2%26lt%3B%3D72)))%2C(C2%2B0.5)%2C(IF((AND((700%26lt%3B%3DC2%2CC2%26lt%3B%3D749)%2C(K2%26gt%3B72)))%2C(E2%2B1)%2C(IF((AND((C2%26gt%3B%3D750)%2C(K2%26lt%3B36)))%2C(E2-0.3)%2C(IF((AND((C2%26gt%3B%3D750)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C(IF((AND((C2%26gt%3B%3D750)%2C(61%26lt%3B%3DK2%2CK2%26lt%3B%3D72)))%2C(E2%2B0.5)%2C(IF((AND((C2%26gt%3B%3D750)%2C(K2%26gt%3B72)))%2C(E2%2B1)%2C%22fawk%22)))))))))))))))))))))))))))))))))))))))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2897385%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2897467%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20wrong%20with%20my%20if%20and%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2897467%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199138%22%20target%3D%22_blank%22%3E%40YayiChop%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20didn't%20check%20the%20whole%20formula%20but%20it%20seems%20that%20there%20are%20too%20many%20()%20and%20in%20the%202nd%20IF%20there%20is%20a%20mistake%20(I%20didn't%20check%20further%20than%20this).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(AND((C2%26lt%3B%3D579)%2C(37%26lt%3B%3DK2%3BK2%26lt%3B%3D60)))%3C%2FP%3E%3CP%3Ereplace%20above%20expression%20with%3A%3C%2FP%3E%3CP%3EAND(C2%26lt%3B%3D579%2C37%26lt%3B%3DK2%3BK2%26lt%3B%3D60)%3C%2FP%3E%3CP%3Eand%20the%20result%20will%20be%20value%20of%20E2.%20Please%20see%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2897526%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20wrong%20with%20my%20if%20and%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2897526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199138%22%20target%3D%22_blank%22%3E%40YayiChop%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20didn't%20check%20the%20whole%20formula%20but%20it%20seems%20that%20there%20are%20too%20many%20()%20and%20in%20the%202nd%20IF%20there%20is%20a%20mistake%20(I%20didn't%20check%20further%20than%20this).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(AND((C2%26lt%3B%3D579)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%3C%2FP%3E%3CP%3Ereplace%20above%20expression%20with%3A%3C%2FP%3E%3CP%3EAND(C2%26lt%3B%3D579%2CK2%26gt%3B%3D37%2CK2%26lt%3B%3D60)%3C%2FP%3E%3CP%3Eand%20the%20result%20will%20be%20value%20of%20E2.%20Please%20see%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2897606%22%20slang%3D%22en-US%22%3ERe%3A%20What%20is%20wrong%20with%20my%20if%20and%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2897606%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1199138%22%20target%3D%22_blank%22%3E%40YayiChop%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3BInitial%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF((AND((C2%26lt%3B%3D579)%2C(K2%26lt%3B%3D36)))%2C(E2-0.3)%2C%0A%20(IF((AND((C2%26lt%3B%3D579)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C%0A%20(IF((AND((C2%26lt%3B%3D579)%2C(K2%26gt%3B60)))%2C(E2%2B0.6)%2C%0A%20(IF((AND((580%26lt%3B%3DC2%2CC2%26lt%3B%3D619)%2C(K2%26lt%3B%3D36)))%2C(E2-0.3)%2C%0A%20(IF((AND((580%26lt%3B%3DC2%2CC2%26lt%3B%3D619)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C%0A%20(IF((AND((580%26lt%3B%3DC2%2CC2%26lt%3B%3D619)%2C(K2%26gt%3B60)))%2C(E2%2B0.4)%2C%0A%20(IF((AND((620%26lt%3B%3DC2%2CC2%26lt%3B%3D649)%2C(K2%26lt%3B%3D36)))%2C(E2-0.3)%2C%0A%20(IF((AND((620%26lt%3B%3DC2%2CC2%26lt%3B%3D649)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C%0A%20(IF((AND((620%26lt%3B%3DC2%2CC2%26lt%3B%3D649)%2C(K2%26gt%3B60)))%2C(E2%2B0.6)%2C%0A%20(IF((AND((650%26lt%3B%3DC2%2CC2%26lt%3B%3D699)%2C(K2%26lt%3B%3D36)))%2C(E2-0.3)%2C%0A%20(IF((AND((650%26lt%3B%3DC2%2CC2%26lt%3B%3D699)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C%0A%20(IF((AND((650%26lt%3B%3DC2%2CC2%26lt%3B%3D699)%2C(61%26lt%3B%3DK2%2CK2%26lt%3B%3D72)))%2C(E2%2B0.3)%2C%0A%20(IF((AND((650%26lt%3B%3DC2%2CC2%26lt%3B%3D699)%2C(K2%26gt%3B72)))%2C(E2%2B1)%2C%0A%20(IF((AND((700%26lt%3B%3DC2%2CC2%26lt%3B%3D749)%2C(K2%26lt%3B36)))%2C(E2-0.3)%2C%0A%20(IF((AND((700%26lt%3B%3DC2%2CC2%26lt%3B%3D749)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C%0A%20(IF((AND((700%26lt%3B%3DC2%2CC2%26lt%3B%3D749)*(61%26lt%3B%3DK2%2CK2%26lt%3B%3D72)))%2C(C2%2B0.5)%2C%0A%20(IF((AND((700%26lt%3B%3DC2%2CC2%26lt%3B%3D749)%2C(K2%26gt%3B72)))%2C(E2%2B1)%2C%0A%20(IF((AND((C2%26gt%3B%3D750)%2C(K2%26lt%3B36)))%2C(E2-0.3)%2C%0A%20(IF((AND((C2%26gt%3B%3D750)%2C(37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)))%2CE2%2C%0A%20(IF((AND((C2%26gt%3B%3D750)%2C(61%26lt%3B%3DK2%2CK2%26lt%3B%3D72)))%2C(E2%2B0.5)%2C%0A%20(IF((AND((C2%26gt%3B%3D750)%2C(K2%26gt%3B72)))%2C(E2%2B1)%2C%22fawk%22)))))))))))))))))))))))))))))))))))))))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ereturns%20some%20result%2C%20but%20it's%20overcomplicated.%20At%20least%20you%20may%20remove%20extra%20perenties%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%20AND(C2%26lt%3B%3D579%2CK2%26lt%3B%3D36)%2C%20%20%20%20%20%20%20%20%20%20%20E2-0.3%2C%0A%20IF(%20AND(C2%26lt%3B%3D579%2C37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)%2C%20%20%20%20E2%2C%0A%20IF(%20AND(C2%26lt%3B%3D579%2CK2%26gt%3B60)%2C%20%20%20%20%20%20%20%20%20%20%20%20E2%2B0.6%2C%0A%20IF(%20AND(580%26lt%3B%3DC2%2CC2%26lt%3B%3D619%2C%20K2%26lt%3B%3D36)%2C%20%20E2-0.3%2C%0A%20IF(%20AND(580%26lt%3B%3DC2%2CC2%26lt%3B%3D619%2C%2037%26lt%3B%3DK2%2CK2%26lt%3B%3D60)%2CE2%2C%0A%20IF(%20AND(580%26lt%3B%3DC2%2CC2%26lt%3B%3D619%2C%20K2%26gt%3B60)%2C%20%20%20E2%2B0.4%2C%0A%20IF(%20AND(620%26lt%3B%3DC2%2CC2%26lt%3B%3D649%2C%20K2%26lt%3B%3D36)%2C%20%20E2-0.3%2C%0A%20IF(%20AND(620%26lt%3B%3DC2%2CC2%26lt%3B%3D649%2C%2037%26lt%3B%3DK2%2CK2%26lt%3B%3D60)%2CE2%2C%0A%20IF(%20AND(620%26lt%3B%3DC2%2CC2%26lt%3B%3D649%2C%20K2%26gt%3B60)%2C%20%20%20E2%2B0.6%2C%0A%20IF(%20AND(650%26lt%3B%3DC2%2CC2%26lt%3B%3D699%2C%20K2%26lt%3B%3D36)%2C%20%20E2-0.3%2C%0A%20IF(%20AND(650%26lt%3B%3DC2%2CC2%26lt%3B%3D699%2C%2037%26lt%3B%3DK2%2CK2%26lt%3B%3D60)%2CE2%2C%0A%20IF(%20AND(650%26lt%3B%3DC2%2CC2%26lt%3B%3D699%2C%2061%26lt%3B%3DK2%2CK2%26lt%3B%3D72)%2C%20E2%2B0.3%2C%0A%20IF(%20AND(650%26lt%3B%3DC2%2CC2%26lt%3B%3D699%2C%20K2%26gt%3B72)%2C%20%20%20E2%2B1%2C%0A%20IF(%20AND(700%26lt%3B%3DC2%2CC2%26lt%3B%3D749%2C%20K2%26lt%3B36)%2C%20%20%20E2-0.3%2C%0A%20IF(%20AND(700%26lt%3B%3DC2%2CC2%26lt%3B%3D749%2C%2037%26lt%3B%3DK2%2CK2%26lt%3B%3D60)%2CE2%2C%0A%20IF(%20AND(700%26lt%3B%3DC2%2CC2%26lt%3B%3D749%2C%2061%26lt%3B%3DK2%2CK2%26lt%3B%3D72)%2C%20C2%2B0.5%2C%0A%20IF(%20AND(700%26lt%3B%3DC2%2CC2%26lt%3B%3D749%2C%20K2%26gt%3B72)%2C%20%20%20E2%2B1%2C%0A%20IF(%20AND(C2%26gt%3B%3D750%2CK2%26lt%3B36)%2C%20%20%20%20%20%20%20%20%20%20%20%20E2-0.3%2C%0A%20IF(%20AND(C2%26gt%3B%3D750%2C37%26lt%3B%3DK2%2CK2%26lt%3B%3D60)%2C%20%20%20%20E2%2C%0A%20IF(%20AND(C2%26gt%3B%3D750%2C61%26lt%3B%3DK2%2CK2%26lt%3B%3D72)%2C%20%20%20%20E2%2B0.5%2C%0A%20IF(%20AND(C2%26gt%3B%3D750%2CK2%26gt%3B72)%2C%20%20%20%20%20%20%20%20%20%20%20%20E2%2B1%2C%0A%20%20%20%20%20%22fawk%22)))))))))))))))))))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eresult%20will%20be%20the%20same.%3C%2FP%3E%0A%3CP%3EI'm%20almost%20sure%20it%20could%20be%20simplified%20more%2C%20especially%20if%20use%20helper%20data%20range%20with%20parameters%2C%20but%20reverse%20engineering%20without%20business%20logic%20defined%20is%20bit%20time%20consuming.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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