SOLVED

Conditional Formatting in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2804200%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2804200%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20Status%20field.%26nbsp%3B%20I%20am%20trying%20to%20automatically%20select%20an%20option%20based%20on%20the%20following%3A%3C%2FP%3E%3CUL%3E%3CLI%3EWhen%20%3CSTRONG%3EE10%3C%2FSTRONG%3E%20(Assigned%20To)%20is%20NOT%20blank%20AND%20%3CSTRONG%3EF10%3C%2FSTRONG%3E%20(Progress)%20is%20blank%20OR%200%25%2C%20then%20%3CSTRONG%3EK10%3C%2FSTRONG%3E%20(Status)%20should%20equal%20%22Assigned%22%3C%2FLI%3E%3CLI%3EWhen%20%3CSTRONG%3EE10%3C%2FSTRONG%3E%20(Assigned%20To)%20is%20blank%20AND%20%3CSTRONG%3EF10%3C%2FSTRONG%3E%20(Progress)%20is%20blank%20OR%200%25%2C%20%3CSTRONG%3EK10%3C%2FSTRONG%3E%20(Status)%20should%20equal%20%22Unassigned%22%3C%2FLI%3E%3CLI%3EWhen%20%3CSTRONG%3EF10%3C%2FSTRONG%3E%20(Progress)%20is%20greater%20than%200%25%2C%20%3CSTRONG%3EK10%3C%2FSTRONG%3E%20(Status)%20should%20equal%20%22Working%22%3C%2FLI%3E%3CLI%3EWhen%20%3CSTRONG%3EF10%3C%2FSTRONG%3E%20(Progress%20is%20less%20than%2075%25%20%3CSTRONG%3EAND%20J10%3C%2FSTRONG%3E%20(Days%20Remaining)%20is%20%26lt%3B%3D%205%2C%20then%20%3CSTRONG%3EK10%3C%2FSTRONG%3E%20(Status)%20should%20equal%20%22At%20Risk%22%3C%2FLI%3E%3CLI%3EWhen%20%3CSTRONG%3EF10%3C%2FSTRONG%3E%20(Progress)%20is%20less%20than%2075%25%20%3CSTRONG%3EAND%20J10%3C%2FSTRONG%3E%20(Days%20Remaining)%20is%200%2C%20then%20%3CSTRONG%3EK10%3C%2FSTRONG%3E%20(Status)%20should%20equal%20%22Behind%20Schedule%22%3C%2FLI%3E%3CLI%3EWhen%20%3CSTRONG%3EF10%3C%2FSTRONG%3E%20(Progress)%20is%20equal%20to%20100%25%2C%20then%20%3CSTRONG%3EK10%3C%2FSTRONG%3E%20(Status)%20should%20equal%20%22Complete%22%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20keep%20trying%20to%20write%20IF%20statements%20but%20the%20values%20are%20not%20being%20selected.%26nbsp%3B%20Can%20someone%20please%20advise%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2804200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2804226%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2804226%22%20slang%3D%22en-US%22%3EIs%20this%20done%20in%20Conditional%20Formatting%20or%20is%20this%20a%20formula%3F%20Please%20advise%20and%20offer%20suggestion.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2804351%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2804351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1172511%22%20target%3D%22_blank%22%3E%40tracie3320%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(F10%3D0%25%2CIF(E10%3D%22%22%2C%22Unassigned%22%2C%22Assigned%22)%2CIF(F10%3D100%25%2C%22Complete%22%2CIF(F10%26lt%3B75%25%2CIF(J10%3D0%2C%22Behind%20Schedule%22%2CIF(J10%26lt%3B%3D5%2C%22At%20Risk%22%2C%22Working%22))%2C%22Working%22)))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a Status field.  I am trying to automatically select an option based on the following:

  • When E10 (Assigned To) is NOT blank AND F10 (Progress) is blank OR 0%, then K10 (Status) should equal "Assigned"
  • When E10 (Assigned To) is blank AND F10 (Progress) is blank OR 0%, K10 (Status) should equal "Unassigned"
  • When F10 (Progress) is greater than 0%, K10 (Status) should equal "Working"
  • When F10 (Progress is less than 75% AND J10 (Days Remaining) is <= 5, then K10 (Status) should equal "At Risk"
  • When F10 (Progress) is less than 75% AND J10 (Days Remaining) is 0, then K10 (Status) should equal "Behind Schedule"
  • When F10 (Progress) is equal to 100%, then K10 (Status) should equal "Complete"

I keep trying to write IF statements but the values are not being selected.  Can someone please advise?

2 Replies
best response confirmed by tracie3320 (Occasional Contributor)
Solution

@tracie3320 

How about:

 

=IF(F10=0%,IF(E10="","Unassigned","Assigned"),IF(F10=100%,"Complete",IF(F10<75%,IF(J10=0,"Behind Schedule",IF(J10<=5,"At Risk","Working")),"Working")))

Thank you! That works fine.