Home

Excel Formula, combining two IF statements

%3CLINGO-SUB%20id%3D%22lingo-sub-113350%22%20slang%3D%22en-US%22%3EExcel%20Formula%2C%20combining%20two%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-113350%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20string%20together%20two%20IF%20statements%2C%26nbsp%3B%3DIF(C7%26gt%3B%3D70%2C%20C7*0.5)%20and%26nbsp%3B%3DIF(C7%26lt%3B%3D69%2C%20C7*0.65)%2C%20please%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-113350%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-301657%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%2C%20combining%20two%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-301657%22%20slang%3D%22en-US%22%3E%3CP%3EOk%2C%20so%20let's%20organize%20your%20criteria%3A%3C%2FP%3E%3CP%3EN20%20%26lt%3B%3D%20100%3C%2FP%3E%3CP%3EN20%20%26gt%3B%20100%3C%2FP%3E%3CP%3EN20%20%26lt%3B%3D%20200%3C%2FP%3E%3CP%3EN20%20%26gt%3B%20200%3C%2FP%3E%3CP%3EN20%20%26lt%3B%3D%20300%3C%2FP%3E%3CP%3EN20%20%26gt%3B%20300%3C%2FP%3E%3CP%3EThe%20initial%20problem%20is%20that%26nbsp%3Bthe%20criteria%20overlaps.%20For%20instance%2C%20if%20N20%20%3D%20150%2C%20should%20it%20meet%20the%20criteria%20for%20N20%20%26gt%3B%20100%20or%20N20%20%26lt%3B%3D%20200%3F%20If%20N20%20%3D301%2C%20should%20it%20meet%20the%20criteria%20for%20N20%20%26gt%3B%20100%2C%20N20%20%26gt%3B%20200%2C%20or%20N20%20%26gt%3B%20300%3F%3C%2FP%3E%3CP%3EThe%20criteria%20should%20usually%20be%20something%20like%3A%3C%2FP%3E%3CP%3EN20%20%26gt%3B100%20AND%20N20%20%26lt%3B%3D150%3C%2FP%3E%3CP%3EN20%20%26gt%3B150%20AND%20N20%20%26lt%3B%3D%20200%3C%2FP%3E%3CP%3EN20%20%26gt%3B200%20AND%20N20%20%26lt%3B%3D%20250%3C%2FP%3E%3CP%3Eand%20so%20on.%20Can%20you%20clarify%20your%20criteria%20statements%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-299539%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%2C%20combining%20two%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299539%22%20slang%3D%22en-US%22%3EI%20need%20to%20string%20together%20IF%20statements%20like%20this%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(N20%26lt%3B%3D200%2CN20*8.11%2CIF(N20%26gt%3B200%2C(200*8.11)%2B(N20-200)*10.2))%3CBR%20%2F%3E%3DIF(N20%26lt%3B%3D300%2CN20*10.2%2CIF(N20%26gt%3B300%2C(300*10.2)%2B(N20-300)*14.08))%3CBR%20%2F%3E%3DIF(N20%26lt%3B%3D100%2CN20*5.79%2CIF(N20%26gt%3B100%2C(100*5.79)%2B(N20-100)*8.11))%3CBR%20%2F%3Ethanks%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-113531%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%2C%20combining%20two%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-113531%22%20slang%3D%22en-US%22%3E%3CP%3EAnd%20quite%20often%20better%20to%26nbsp%3Bavoid%20IF(S)%20even%20if%20that's%20first%20what%20we%20have%20in%20mind%20-%20Detlef%20gave%20good%20examples.%20Alternative%20could%20be%20more%20compact%2C%20effective%20and%20maintainable.%26nbsp%3B%20Assume%20if%20in%20IFS%20from%20previous%20post%20we%20have%20some%20complex%20expression%20instead%20of%20C7.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-113523%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%2C%20combining%20two%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-113523%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20general%2C%20it's%20good%20practice%20to%20arrange%20your%20IF%20statements%20into%20an%20IF%2C%20THEN%2C%20ELSE%20(If%20not)%20order.%20For%20instance%2C%3C%2FP%3E%3CP%3EIf%20C7%26gt%3B%3D70%3C%2FP%3E%3CP%3EThen%20C7*0.5%3C%2FP%3E%3CP%3EElse%20(If%20not%2C%20then)%20C7*0.65%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20always%20translates%20well%20to%20the%20IF%20function%20in%20Excel%2C%20which%20is%20IF(%22If%22%20condition%2C%20%22Then%22%20condition%2C%20%22Else%22%20condition)%20or%20%3DIF(C7%26gt%3B%3D70%2CC7*0.5%2CC7*0.65)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20be%20aware%20that%20it%20will%20always%20follow%20the%20first%20condition%20that%20meets%20the%20criteria%2C%20in%20this%20case%20multiplying%20it%20by%200.5.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20take%20a%20look%20at%20IFS%2C%20which%20is%20good%20for%20multiple%20IF%20statements.%20The%20format%20is%20the%20condition%20followed%20by%20the%20action%2C%20followed%20by%20a%20new%20condition%20and%20subsequent%20action%20and%20on%20and%20on.%20For%20example%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS(C7%26gt%3B70%2CC7*0.5%2CC7%26gt%3B60%2CC7*0.65%2CC7%26gt%3B50%2CC7*.73%2CC7%26gt%3B40%2CC7*.78%2CTRUE%2CC7*.82)%20(Where%20the%20%22TRUE%22%20will%20catch%20all%20cases%20that%20don't%20meet%20the%20other%20criteria).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20luck!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-113498%22%20slang%3D%22en-US%22%3ERE%3A%20Excel%20Formula%2C%20combining%20two%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-113498%22%20slang%3D%22en-US%22%3E%3CP%3E%3DIF(C7%26lt%3B%3D69%2CSUM(C7*0.65)%2CIF(C7%26gt%3B%3D70%2CSUM(C7*0.5)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-113464%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%2C%20combining%20two%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-113464%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-113405%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%2C%20combining%20two%20IF%20statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-113405%22%20slang%3D%22en-US%22%3E%3CP%3ERosa%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20are%20two%20solutions%3A%3C%2FP%3E%3CPRE%3E%3D((C7%26gt%3B%3D70)*0.5%2B(C7%26lt%3B%3D69)*0.65)*C7%0A%3DLOOKUP(C7%2C%7B0%3B70%7D%2C%7B0.65%3B0.5%7D)*C7%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Rosa Hernandez
New Contributor

I need to string together two IF statements, =IF(C7>=70, C7*0.5) and =IF(C7<=69, C7*0.65), please help

7 Replies

Rosa,

 

here are two solutions:

=((C7>=70)*0.5+(C7<=69)*0.65)*C7
=LOOKUP(C7,{0;70},{0.65;0.5})*C7

Thank you!

=IF(C7<=69,SUM(C7*0.65),IF(C7>=70,SUM(C7*0.5)))

In general, it's good practice to arrange your IF statements into an IF, THEN, ELSE (If not) order. For instance,

If C7>=70

Then C7*0.5

Else (If not, then) C7*0.65

 

This always translates well to the IF function in Excel, which is IF("If" condition, "Then" condition, "Else" condition) or =IF(C7>=70,C7*0.5,C7*0.65)

 

Just be aware that it will always follow the first condition that meets the criteria, in this case multiplying it by 0.5.

 

Also take a look at IFS, which is good for multiple IF statements. The format is the condition followed by the action, followed by a new condition and subsequent action and on and on. For example,

 

=IFS(C7>70,C7*0.5,C7>60,C7*0.65,C7>50,C7*.73,C7>40,C7*.78,TRUE,C7*.82) (Where the "TRUE" will catch all cases that don't meet the other criteria).

 

Good luck!

 

 

And quite often better to avoid IF(S) even if that's first what we have in mind - Detlef gave good examples. Alternative could be more compact, effective and maintainable.  Assume if in IFS from previous post we have some complex expression instead of C7.

I need to string together IF statements like this

=IF(N20<=200,N20*8.11,IF(N20>200,(200*8.11)+(N20-200)*10.2))
=IF(N20<=300,N20*10.2,IF(N20>300,(300*10.2)+(N20-300)*14.08))
=IF(N20<=100,N20*5.79,IF(N20>100,(100*5.79)+(N20-100)*8.11))
thanks

Ok, so let's organize your criteria:

N20 <= 100

N20 > 100

N20 <= 200

N20 > 200

N20 <= 300

N20 > 300

The initial problem is that the criteria overlaps. For instance, if N20 = 150, should it meet the criteria for N20 > 100 or N20 <= 200? If N20 =301, should it meet the criteria for N20 > 100, N20 > 200, or N20 > 300?

The criteria should usually be something like:

N20 >100 AND N20 <=150

N20 >150 AND N20 <= 200

N20 >200 AND N20 <= 250

and so on. Can you clarify your criteria statements?