 • 382K Members
• 6,209 Online
• 399K Conversations

New Contributor

# Excel Formula, combining two IF statements

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

# Re: Excel Formula, combining two IF statements

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!

# RE: Excel Formula, combining two IF statements

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

# Re: Excel Formula, combining two IF statements

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!

# Re: Excel Formula, combining two IF statements

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.

# Re: Excel Formula, combining two IF statements

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

# Re: Excel Formula, combining two IF statements

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?

Related Conversations