Multiple IF AND statements excel

Question

I need to write an  if  statement in Excel based on text in two different cells    If E2   in play    and F2   closed       output 3  If E2   in play    and F2   suspended    output 2 If E2   In Play    and F2   Null         output 1  If E2   Pre-Play   and F2   Null         output -1 If E2   Completed  and F2   Closed       output 2 If E2   Suspended  and F2   Null         output 3 If anything else output -2   where Null is no value in the cell  I was trying to do this with the code below but I can t seem to get two or more IF AND statements working together   How can I solve this problem    IF AND E2  In Play  F2  Closed   3  -2   IF AND E2  In Play  F2   Suspended   3 -2

User · Answer

Making these 2 communicate   IF OR AND MID K27 6 1   N   MID K27 6 1   C    MID K27 6 1   H    MID K27 6 1   I    MID K27 6 1   B    MID K27 6 1   F    MID K27 6 1   L    MID K27 6 1   M    MID K27 6 1   P    MID K27 6 1   R    MID K27 6 1   P   ISTEXT G27   61   AND RIGHT K27 2  G27    Good   Review     IF AND RIGHT K27 2  G27   Good   Review

User · Answer

With your ANDs you shouldn t have a FALSE value -2  until right at the end  e g  with just 2 ANDs   IF AND E2  In Play  F2  Closed   3 IF AND E2  In Play  F2   Suspended   3 -2    although it might be better with a combination of nested IFs and ANDs - try like this for the full formula  Edited - thanks David    IF E2  In Play  IF F2  Closed  3 IF F2  Suspended  2 IF F2  Null  1    IF AND E2  Pre-play  F2  Null   -1 IF AND E2  Completed  F2  Closed   2 IF AND E2  Pre-play  F2  Null   3 -2      To avoid a long formula like the above you could create a table with all E2 possibilities in a column like K2 K5 and all F2 possibilities in a row like L1 N1 then fill in the required results in L2 N5 and use this formula   INDEX  L 2  N 5 MATCH E2  K 2  K 5 0  MATCH F2  L 1  N 1 0

User · Answer

Try the following    IF OR E2  in play  E2  pre play  E2  complete  E2  suspended    IF E2  in play  IF F2  closed  3 IF F2  suspended  2 IF ISBLANK F2  1 -2     IF E2  pre play  IF ISBLANK F2  -1 -2  IF E2  completed  IF F2  closed  2 -2   IF E2  suspended  IF ISBLANK F2  3 -2      -2

User · Answer

Consider that you have multiple  tests   e g      If E2    in play  and F2    closed   output 3 If E2    in play  and F2    suspended   output 2 Etc     What you really need to do is put successive tests in the False argument  You re presently trying to separate each test by a comma  and that won t work   Your first three tests can all be joined in one expression like    IF E2  In Play  IF F2  Closed  3 IF F2  suspended  2 IF F2  Null  1      Remembering that each successive test needs to be the nested FALSE argument of the preceding test  you can do this    IF E2  In Play  IF F2  Closed  3 IF F2  suspended  2 IF F2  Null  1    IF AND E2  Pre-Play  F2  Null   -1 IF AND E2  completed  F2  closed   2 IF AND E2  suspended  F2  Null   3 -2

[excel] Multiple IF AND statements excel

The answer is

Examples related to excel

Tags