Forum Discussion
Rosa Hernandez
Oct 04, 2017Copper 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
Bryant Boyer
Oct 04, 2017Brass Contributor
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!
MarieHelena
Jan 18, 2020Copper Contributor
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!
- SergeiBaklanJan 18, 2020MVP
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.
- MarieHelenaJan 18, 2020Copper Contributor
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 - SergeiBaklanJan 18, 2020MVP
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 ) )