How to use multiple IF and AND functions for symbol formula

Curtis

Hello Everyone,

I have a basic project sheet with Start Date, End Date, % Complete Columns.

I would like a formula for Symbols to do the following:

Gray = If todays date is less than Start Date

Red = If todays date is greater than due date AND % Complete is less than 100

Yellow = If todays date is less than due date AND % Complete is blank or 0

Green = If % Complete = 100

Thank you for your help! 😊

Paul Newcome

Try something like this...

=IF([% Complete]@row = 1, "Green", IF([Start Date]@row> TODAY(), "Gray", IF([Due Date]@row< TODAY(), "Red", "Yellow")))

Paul Newcome

Try something like this...

=IF([% Complete]@row = 1, "Green", IF([Start Date]@row> TODAY(), "Gray", IF([Due Date]@row< TODAY(), "Red", "Yellow")))

Curtis

Thank you!

Paul Newcome

Happy to help. 👍️

anilkrag1984

Hi Team, I need help with similar issue can anyone advise?

=IF(AND(APIC@row <> "", [CN Devices]@row <> ""), 1, IF(AND(APIC@row = "", Nexus@row > 9), 1))

Paul Newcome

@anilkrag77641 What issue are you running into? Your syntax appears correct.

anilkrag1984

Hi Paul,

My requirement is, I have 2 columns "APIC" and "Nexus" I need the data in CN Threshold column as per the below 2 conditions -

1) If value in APIC cell in not blank then CN Threshold should return 1 else 0

2) If Value on Nexus is then CN threshold should result 1 else 0

I am using below formula

=IF(AND(APIC@row <> "", [CN Devices]@row <> ""), 1, IF(AND(APIC@row = "", Nexus@row > 9), 1))

Paul Newcome

@anilkrag1984 I am still unsure of what you are wanting to accomplish. Your last comment does not say anything about the [CN Devices] column, and you left out the requirement for the Nexus column.

anilkrag1984

Hi Paul

Sorry for the confusion my requirement is if APIC cell is not blank then return 1 in CN Threshold and if value in Nexus cell is more than 9 then return 1 in CN threshold else return 0.

Any of the condition satisfies either APIC is not blank or Nexus is more than 9 then CN threshold should return 1 else 0.

Paul Newcome

@anilkrag1984 Give this a try:

=IF(OR(APIC@row <> "", Nexus@row > 9), 1)

anilkrag1984

Thank you Paul, it worked.

Paul Newcome

@anilkrag1984 Happy to help. 👍️