Share via

IF THEN ELSE statement

Anonymous
2011-01-20T23:32:52+00:00

I've been pulling my hair out most of the day trying to get this to work. I have a worksheet that I am using to track requirements for the US Army Expert Field Medical Badge. I have 11 columns of data. I need column to look at each column and if the requirement is meet, then I need it to return "Yes". If the requirement is not met, then I need it to say "No". However, if one of the columns is blank, I need it to say "Yes".

What I came up with is not working because the first and last row should be saying "No":

=IF(AND(COUNTBLANK(K2:V2)>0,(OR(K2>179,L2="G",M2="G", N2="G", O2="G", P2="G", Q2="G", R2>2, S2>2, T2>44,U2>2,V2<=V207))),"Yes","No")

Still Competing? APFT Score Weapons Qualification CPR TC3 (11 out of 14) EVAC (8 out of 10) COMMO (4 out of 5) Warrior Skills (10 out of 13) Day Land Navigation (3 of 4) Night Land Navigation (3 of 4) Written Test (45 out of 60) Written Test Re-test (45 out of 60) Road March (HH:MM:SS)
Yes 293 G N
Yes 215 G G
Yes 275 G G
Yes 240 G G
Yes 322 G N

 Thanks for the help!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-01-21T01:09:32+00:00

Try, in row 16, this formula

=IF(COUNTBLANK(K16:V16)>0,"Yes",IF(AND(K16>179,L16="G",M16="G", N16="G", O16="G", P16="G", Q16="G", R16>2, S16>2, T16>44,U16>2,V16>=V221),"Yes","No"))

IF it works, then copy it up and down your column.

Shorter:

=IF(COUNTBLANK(K16:V16)>0,"Yes",IF(AND(K16>179,COUNTIF(L16:Q16,"G")=6, R16>2, S16>2, T16>44,U16>2,V16>=V221),"Yes","No"))

HTH, Bernie

Was this answer helpful?

0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-01-21T00:13:14+00:00

    If I use the following:

    =IF(AND(K16>179,L16="G",M16="G", N16="G", O16="G", P16="G", Q16="G", R16>2, S16>2, T16>44,U16>2,V16>=V221),"Yes","No")

    then the formula works, sort of. If a cell is blank then I get a "No".

    What I need to add is if any cell from K to V is blank then the answer should be "Yes".

    Hope that clarifies.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-01-21T00:07:51+00:00

    Your words " if one of the columns is blank, I need it to say "Yes""  lead me to believe that your should change the AND to OR:

    =IF(OR(COUNTBLANK(K2:V2)>0,(OR(K2>179,L2="G",M2="G", N2="G", O2="G", P2="G", Q2="G", R2>2, S2>2, T2>44,U2>2,V2<=V207))),"Yes","No")

    But to better understand why your last row is giving a "Yes" rather than "No", we would need the entire last row's values (with identification of the columns) - though is seems that 322 is greater than 179 - OR - maybe you need to change  V2<=V207 to V2<=V$207

    • OR......   Though to really help, we would need a description (in words) or what your requirements are.

    Nate that you can shorten the check for "G" parts by using COUNTIF:

    =IF(OR(COUNTBLANK(K2:V2)>0,(OR(K2>179,COUNTIF(L2:Q2,"G")>0, R2>2, S2>2, T2>44,U2>2,V2<=V207))),"Yes","No")

    Another technique is to set the requirement for each column or set of columns by individual formulas that return TRUE or FALSE - when they all return TRUE (Checking with another formula), then you can say "Yes"  That makes the logic a little clearer.

    HTH, Bernie

    Was this answer helpful?

    0 comments