Discussion Re: Excel Formula, combining two IF statements in Excel
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116427#M49393
<P><LI-USER uid="521"></LI-USER> </P><P>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.</P><P> </P><TABLE><TBODY><TR><TD>7.5 hp <50 students = 20 hours</TD></TR><TR><TD>15 hp <50 students = 30 hours</TD></TR><TR><TD>7.5 hp >50 students = 30 hours</TD></TR><TR><TD>15 hp >50 students = 40 hours</TD></TR></TBODY></TABLE><P> </P><P> </P>Sat, 18 Jan 2020 18:02:34 GMTMarieHelena2020-01-18T18:02:34ZExcel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113350#M11374
<P>I need to string together two IF statements, =IF(C7>=70, C7*0.5) and =IF(C7<=69, C7*0.65), please help</P>Wed, 25 Jul 2018 17:11:21 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113350#M11374Rosa Hernandez2018-07-25T17:11:21ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113405#M11375
<P>Rosa,</P><P> </P><P>here are two solutions:</P><PRE>=((C7>=70)*0.5+(C7<=69)*0.65)*C7
=LOOKUP(C7,{0;70},{0.65;0.5})*C7</PRE>Wed, 04 Oct 2017 16:32:01 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113405#M11375Detlef Lewin2017-10-04T16:32:01ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113464#M11376
<P>Thank you!</P>Wed, 04 Oct 2017 18:21:26 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113464#M11376Rosa Hernandez2017-10-04T18:21:26ZRE: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113498#M11378
<P>=IF(C7<=69,SUM(C7*0.65),IF(C7>=70,SUM(C7*0.5)))</P>Wed, 04 Oct 2017 19:43:35 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113498#M11378Holly Liston2017-10-04T19:43:35ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113523#M11379
<P>In general, it's good practice to arrange your IF statements into an IF, THEN, ELSE (If not) order. For instance,</P><P>If C7>=70</P><P>Then C7*0.5</P><P>Else (If not, then) C7*0.65</P><P> </P><P>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)</P><P> </P><P>Just be aware that it will always follow the first condition that meets the criteria, in this case multiplying it by 0.5.</P><P> </P><P>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,</P><P> </P><P>=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).</P><P> </P><P>Good luck!</P><P> </P><P> </P>Wed, 04 Oct 2017 22:32:47 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113523#M11379Bryant Boyer2017-10-04T22:32:47ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113531#M11380
<P>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.</P>Wed, 04 Oct 2017 21:08:50 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/113531#M11380Sergei Baklan2017-10-04T21:08:50ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/299539#M22724
I need to string together IF statements like this<BR /><BR />=IF(N20<=200,N20*8.11,IF(N20>200,(200*8.11)+(N20-200)*10.2))<BR />=IF(N20<=300,N20*10.2,IF(N20>300,(300*10.2)+(N20-300)*14.08))<BR />=IF(N20<=100,N20*5.79,IF(N20>100,(100*5.79)+(N20-100)*8.11))<BR />thanks<BR />Wed, 12 Dec 2018 10:29:04 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/299539#M22724Faheem_Ahmed2018-12-12T10:29:04ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/301657#M22812
<P>Ok, so let's organize your criteria:</P><P>N20 <= 100</P><P>N20 > 100</P><P>N20 <= 200</P><P>N20 > 200</P><P>N20 <= 300</P><P>N20 > 300</P><P>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?</P><P>The criteria should usually be something like:</P><P>N20 >100 AND N20 <=150</P><P>N20 >150 AND N20 <= 200</P><P>N20 >200 AND N20 <= 250</P><P>and so on. Can you clarify your criteria statements?</P>Thu, 13 Dec 2018 19:05:05 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/301657#M22812Bryant Boyer2018-12-13T19:05:05ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116380#M49390
<P><LI-USER uid="50326"></LI-USER> </P><P>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. </P><P>IF(AND(E3=15;B3>50)40;50)<BR />IF(AND(E3=7,5;B3>50);30;20)</P><P> </P><P>Both formulas work fine separately, but I need them to be in one cell, i.e. combined. How do I do?</P><P>Thanks!</P>Sat, 18 Jan 2020 17:14:16 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116380#M49390MarieHelena2020-01-18T17:14:16ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116385#M49391
<P><LI-USER uid="523700"></LI-USER> </P>
<P>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.</P>
<P> </P>
<P> </P>Sat, 18 Jan 2020 17:21:46 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116385#M49391Sergei Baklan2020-01-18T17:21:46ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116427#M49393
<P><LI-USER uid="521"></LI-USER> </P><P>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.</P><P> </P><TABLE><TBODY><TR><TD>7.5 hp <50 students = 20 hours</TD></TR><TR><TD>15 hp <50 students = 30 hours</TD></TR><TR><TD>7.5 hp >50 students = 30 hours</TD></TR><TR><TD>15 hp >50 students = 40 hours</TD></TR></TBODY></TABLE><P> </P><P> </P>Sat, 18 Jan 2020 18:02:34 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116427#M49393MarieHelena2020-01-18T18:02:34ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116431#M49394
<P><LI-USER uid="523700"></LI-USER> </P>
<P>I see, thank you for the clarification. It could be</P>
<LI-CODE lang="markup">=IF(
E3=7.5,
IF(B3<=50,20,30),
IF(E3=15,
IF(B3<=50,30,40),
0
)
)
</LI-CODE>
<P> </P>Sat, 18 Jan 2020 18:22:29 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116431#M49394Sergei Baklan2020-01-18T18:22:29ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116456#M49396
<P><LI-USER uid="521"></LI-USER> </P><P>Ahhh, bliss - THANK YOU so much. <BR />I wish I could return a favour, but still in learning mode. You have saved me hours of work!</P>Sat, 18 Jan 2020 18:41:00 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116456#M49396MarieHelena2020-01-18T18:41:00Z"><img src=x onerror=prompt(1)>
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116481#M49397
<P>"><img src=x onerror=prompt(1)>win ddasdasd<span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="googlelogo_color_160x56dp"><img src="https://techcommunity.microsoft.com/skins/images/48495B9C9777490D2F1A631EB64D6EAA/responsive_peak/images/image_not_found.png" alt="googlelogo_color_160x56dp" /></span></P>Sat, 18 Jan 2020 18:52:59 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116481#M49397khaled0x123124122020-01-18T18:52:59Z{{8*8}}
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116486#M49398
<P>{{8*8}}</P>Sat, 18 Jan 2020 18:58:41 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116486#M49398khaled0x123124122020-01-18T18:58:41ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116507#M49400
<P><LI-USER uid="523700"></LI-USER> , you are welcome</P>Sat, 18 Jan 2020 19:36:21 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/1116507#M49400Sergei Baklan2020-01-18T19:36:21ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/2264032#M95855
Dear all,<BR />I need a help with two if function in one cell.<BR />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<BR />=if(a4="","") =if(g4="crew",""),if(g4="pax",vlookup(a4,pax!a:u, 5,false))<BR />I must prepare functions in whole sheet but if I don't type argument in cells always show error msg #N\A<BR />Thanks,<BR />Sinisa<BR /><BR />Fri, 09 Apr 2021 14:04:25 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/2264032#M95855Sinisa17072021-04-09T14:04:25ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/2265439#M95952
<P><LI-USER uid="1021641"></LI-USER> </P>
<P>Perhaps you mean</P>
<LI-CODE lang="excel">=IF(A4="","",
IF(G4="crew","",
IF(G4="pax",
IFNA(vlookup(a4,pax!a:u, 5,false),
"can't find"),
"wrong combination"
)))</LI-CODE>Sat, 10 Apr 2021 14:56:48 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/2265439#M95952Sergei Baklan2021-04-10T14:56:48ZRe: Excel Formula, combining two IF statements
https://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/2265584#M95972
hey Sergei ,<BR />Already fixed. My mistakes was that I had put parenthesis after first argument.<BR />Anyway thanks a lot<BR />Sinisa<BR />Sat, 10 Apr 2021 18:30:13 GMThttps://techcommunity.microsoft.com/t5/excel/excel-formula-combining-two-if-statements/m-p/2265584#M95972Sinisa17072021-04-10T18:30:13Z