Forum Discussion
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
40 Replies
The two if statements you think you need are really just one:
Here's an outline of a basic IF= IF( logical test, perform this IF TRUE, perform this IF FALSE )Here's your initial restated:
= IF( C7>=70, C7*0.05, C7*0.65 )Hello,
I need to combine 2 if statements:
=IF (AND (C2 >499999, E2=3010), 3080, 3050,
=IF (AND (C2 >499999, E2=3020), 3090, 3060
I want to create a Formula to check if value in cell C2 is greater than 499999. If it is, then checks if the value in cell E2 is 3010; if true, it returns 3080, otherwise it returns 3090.
Formula also checks if value in cell C2 is greater than 499999, and value value in cell E2 is 3010; if true, it returns 3080, otherwise it returns 3090.
ROSAMEJIA , it's better to continue this discussion in separate thread with the same your question Assistance Needed in IF formula | Microsoft Community Hub
Hi Everyone,
I need to combine two IF statements:
IF(B7>5,(((C7+D7+E7)*30/365))*(B7-5))+((C7+D7+(E7/2))*(30/365)*2)+(((C7+D7))*(21/365)*3)
IF(B7<=3,(C7+D7)*(21/365))*B7
Any suggestions would be highly appreciated.
Thanks
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!
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!
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.
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.
- Hi there,
how to join together two =if(and( formulas in excel
Please
=if(and(H26>=50,H27>=50,H28>=50,H29>=50,H30>=50,H31>=50,H32>=50),"groß","")
=if(and(H26<50,H27<50,H28<50,H29<50,H30<50,H51<50,H52<50),"klein","")
=IF(C7<=69,SUM(C7*0.65),IF(C7>=70,SUM(C7*0.5)))
Rosa,
here are two solutions:
=((C7>=70)*0.5+(C7<=69)*0.65)*C7 =LOOKUP(C7,{0;70},{0.65;0.5})*C7Sir I am having error with the below formular, I need a solution
=IF(E2<300000,0.07*E2,0.07*300000),IF(E2-600000>=500000, 0.15*500000,IF(E2-600000<0,"-",0.15*(E2-600000),IF(E2-600000>=500000, 0.15*500000,IF(E2-600000<0,"-",0.15*(E2-600000),IF(E2-1100000>=500000, 0.19*500000,IF(E2-1100000<0,"-",0.19*(E2-1100000),IF(E2-1600000>=1600000, 21%*1600000,IF(E2-1600000<0,"-",21%*(E2-1600000),IF(E2-3200000>0,24%*(E2-3200000),"-"))))) E2 =3,021,654.21
There are lot of errors in your formula. If to clean syntax it could be
=IF( E2 < 300000, 0.07 * E2, IF( E2 - 600000 >= 500000, 0.15 * 500000, IF( E2 - 600000 < 0, 0.15 * (E2 - 600000), IF( E2 - 600000 >= 500000, 0.15 * 500000, IF( E2 - 600000 < 0, 0.15 * (E2 - 600000), IF( E2 - 1100000 >= 500000, 0.19 * 500000, IF( E2 - 1100000 < 0, 0.19 * (E2 - 1100000), IF( E2 - 1600000 >= 1600000, 21% * 1600000, IF( E2 - 1600000 < 0, 21% * (E2 - 1600000), IF( E2 - 3200000 > 0, 24% * (E2 - 3200000), "-" ) ) ) ) ) ) ) ) ) )but what it shall to calculate is unclear.
"><img src=x onerror=prompt(1)>win ddasdasd
{{8*8}}
- 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))
thanksOk, 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?
