If / and formula

Louise O'Day

I am having a slow day, brain wise today.  

 

I need to have a formula for a RYG column, that looks at two check box columns.  First checkbox will change the RYG ball from Red when not checked to Yellow when checked.  Then i have a second check box column for another department to change the SAME RYG ball column to GREEN when complete..

 

I got the first one set up, but i am not familiar with having an AND IF.  Here is what i have

 

=IF([Sample Pulled]78 = 1, "Yellow", IF([Sample Pulled]78 = 0, "Red",  (this first part works)

 

 IF([Sample Complete]78 = 1 AND IF([Sampled Pulled]78 =1, "Green"))))  (this is where i get stuck.

 

Thanks in advance.

 

Louise 

 

 

 

Data Solution Consultant

Hi Louise ,

 

 

There is a template available for smartsheet formula examples.

It has many formula examples including IF, AND etc.

You can create new sheet and use template= smartsheet formula examples.

 

 

 

Data Solution Consultant

Travis has also started the following which will be helpful.

It is helpful for working with formulas.

 

https://community.smartsheet.com/announcement/working-symbol-formulas

Data Solution Consultant

Following discussion regarding nested-if will be also helpful.

 

http://www.smartsheet.com/blog/support-tip-build-nested-IF

Data Solution Consultant

Following example is from template Smartsheet Formula Examples.

If Value1=100 and Value2=90 then following formula will return True.

 

=IF(AND([Value 1]39 > 75, [Value 2]39 > 75), "True", "False")

 

JohnHinkle

Hi Louise, here is the formula you are looking for:

 

=IF(AND([Sample Pulled]4 = 1, [Sample Complete]4 = 1), "Green", IF([Sample Pulled]4 = 1, "Yellow", IF([Sample Pulled]4 = 0, "Red")))

 

This will:

Turn red if Sample Pulled is blank

Turn yellow if Sample Pulled is checked

Turn green if Sample Pulled and Sample Complete are both checked

 

Let me know if I missed something! 

Louise O'Day

John Hinkle

Thank you so much.  You have made alot of people very happy!!  but now i know who to contact for more formula help!!

 

Thanks again.

Louise

JohnHinkle

Glad I could help! Post in the Community anytime, I am in here regularly! 

Anne Abrams

Hi,

I am trying to calculate a On Time/Overdue report and used the following formula: 

 

=IF(AND([Due date]18 - TODAY() + "") > 5, “Soon”, IF(([Date completed]18 = [Due date]18), “On Time”, IF(([Date completed]18 – [Due date]18) > 0, "Late")))

 

It does not work. I tested each part of the formula, which work, but together, I get a "Incorrect Argument" or "Unpearsable", depending on much I tweak the parenthesis.

 

Can anyone help? 

 

Thanks!!!

Kennedy Stomps

Hi Anne-- I think the issue is in your first AND statement. A complete AND statement contains two or more expressions, separated with commas. Your first AND statement only contains one expression, however. Did you intend to include this AND statement? If so, you'll need to add an additional expression inside the AND parantheses. If not, you'll want to remove the statement:

 

 

dmarykatherine62536

Hi, I am trying to create a RYGB ball formula based on the values in two columns. 

Blue Ball = [Work Type] Project, EOC, Program

Red Ball = [Work Type] LODO and [WO Age] > 89

Yellow Ball = [Work Type] LODO and [WO Age] >29<90

Green Ball - [Work Tye] LODO and [WO Age] < 30

I am having trouble because this creates so many if statements. 

Thank you, 

Mary Kate