 New Contributor

18 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?

# Re: Excel Formula, combining two IF statements

I've read all the replies to the intial question without being able to apply it successfully to my two formulas that I want to combine.

IF(AND(E3=15;B3>50)40;50)
IF(AND(E3=7,5;B3>50);30;20)

Both formulas work fine separately, but I need them to be in one cell, i.e. combined. How do I do?

Thanks!

# Re: Excel Formula, combining two IF statements

These two formulas are conflicting in logic if to combine them. Let assume B3 is more than 50. With that if E3=15 first formula returns 40, else 50. At the same time "else" means E3=7.5 as well, in this case second formula shall return 30. So, not clear what shall be returned, 50 or 30.

# Re: Excel Formula, combining two IF statements

Thanks for helping me think. Here is the data I want to get into one formula and one cell - if possible. It is the number of hours (20, 30 or 40) I need the formula to return if it's a 7.5 hp course with more or less than 50 students or if it's a 15 hp course with more or less than 50 students.

 7.5 hp <50 students = 20 hours 15 hp <50 students = 30 hours 7.5 hp >50 students = 30 hours 15 hp >50 students = 40 hours

# Re: Excel Formula, combining two IF statements

I see, thank you for the clarification. It could be

``````=IF(
E3=7.5,
IF(B3<=50,20,30),
IF(E3=15,
IF(B3<=50,30,40),
0
)
)
``````

# Re: Excel Formula, combining two IF statements

Ahhh, bliss - THANK YOU so much.
I wish I could return a favour, but still in learning mode. You have saved me hours of work!

# "><img src=x onerror=prompt(1)>

"><img src=x onerror=prompt(1)>win ddasdasd {{8*8}}

# Re: Excel Formula, combining two IF statements

@MarieHelena , you are welcome

# Re: Excel Formula, combining two IF statements

Dear all,
I need a help with two if function in one cell.
I must take details from two sheet, and for that reason, use one if function. Another if function I need for keep clear cell with error example #N\A
=if(a4="","") =if(g4="crew",""),if(g4="pax",vlookup(a4,pax!a:u, 5,false))
I must prepare functions in whole sheet but if I don't type argument in cells always show error msg #N\A
Thanks,
Sinisa

# Re: Excel Formula, combining two IF statements

Perhaps you mean

``````=IF(A4="","",
IF(G4="crew","",
IF(G4="pax",
IFNA(vlookup(a4,pax!a:u, 5,false),
"can't find"),
"wrong combination"
)))``````

# Re: Excel Formula, combining two IF statements

hey Sergei ,
Already fixed. My mistakes was that I had put parenthesis after first argument.
Anyway thanks a lot
Sinisa