IF(OR(AND?

KaitlinH

I am having trouble with a formula. I am not sure what the heck I am doing wrong here, but I know there are some formula geniuses here that can help me lol.

I am trying to return a Red ball if the category is an output or project and the expected completion date is in the past. If it's not, I want it to pull whatever is in the Health column.

=IF(AND(Category@row = "Project", [Expected Completion Date]@row < (TODAY())), "Red", Health@row), IF(AND(Category@row = "Output", [Expected Completion Date]@row < (TODAY())),"Red", Health@row)

I feel like I need an OR in there somewhere?

AnalyticOwl

Hello Kaitlin!

Based on your description i create a sheet with 4 columns, type (text),date (date),health (symbols) and final status(text) where the formula is getting the result.

Then here is the formula with the nested and / or always put the and first

=IF(AND(date@row < TODAY(), OR(Type@row = "Project", Type@row = "Output")), "Red", health@row)

this will check first if the date is older than today and then combine it with the 2 possible options on the type column if that is the case return the work Red. If you make this one symbol-type column it will trigger the red globe. But if the date is in the future or the type is not the one you want will show what is on the health column at this point.

Hope this could be useful!

Good vibes!

J

Beth B

Hi @KaitlinH,

See if this works:

=IF(OR(AND(Category@row = "Project", [Expected Completion Date]@row < (TODAY(0))), AND(Category@row = "Output", [Expected Completion Date]@row < TODAY(0))), "Red", Heath@row)

AnalyticOwl

Hello Kaitlin!

Based on your description i create a sheet with 4 columns, type (text),date (date),health (symbols) and final status(text) where the formula is getting the result.

Then here is the formula with the nested and / or always put the and first

=IF(AND(date@row < TODAY(), OR(Type@row = "Project", Type@row = "Output")), "Red", health@row)

this will check first if the date is older than today and then combine it with the 2 possible options on the type column if that is the case return the work Red. If you make this one symbol-type column it will trigger the red globe. But if the date is in the future or the type is not the one you want will show what is on the health column at this point.

Hope this could be useful!

Good vibes!

J

Beth B

@AnalyticOwl : Much cleaner than mine above! Nice!!

KaitlinH

That worked :). Thank you both for your help!!!

AnalyticOwl

Thank you @Beth B I love to see many different ways to do a task gives us a bigger view! The interesting part is learning from everywhere!

KaitlinH

@AnalyticOwl Ok, one more question. If I wanted to add another IF statement to change the symbol to Blue if the Status % = 1, where and how would I add that into this formula?

AnalyticOwl

@KaitlinH you can add a nested if and the false condition.

Something like:

=IF(AND(date@row < TODAY(), AND([%]@row = 1), OR(Type@row = "Project", Type@row = "Output")), "Blue", IF(AND(date@row < TODAY(), OR(Type@row = "Project", Type@row = "Output")), "Red", health@row))

In the same setup that i sent you last time i just added a column named % still a number, not a % , but if you transform it to decimal.

Hope this could be useful.

J

KaitlinH

@AnalyticOwl Does this only return a Blue if the date is in the past? The date wouldn't matter for that.

AnalyticOwl

@KaitlinH will give you blue only if the date is the past and the rox value is 1 and if is a Project or output type, after checking that if those did not match then go and check for the only date and type.

Think about the IF like blocks of information, so we check one, block, and if is true give a result, if is not true then execute the other block!

KaitlinH

@AnalyticOwl so if I don't need the date constraint on the Status = 1, "Blue" portion, do I just need to make it =IF(AND([Status %]@row = 1), OR(Category@row = "Project", Category@row = "Output")), "Blue", IF(AND([Expected Completion Date]@row < TODAY(), OR(Category@row = "Project", Category@row = "Output")), "Red", Health@row))

AnalyticOwl

@KaitlinH yep that should work test it! and for the future think about that block structure when you want to build nested if! Have fun!

KaitlinH

@AnalyticOwl that doesn't seem to work. I get #UNPARSEABLE. I wish this made sense in my brain lol.

AnalyticOwl

@KaitlinH

Try this:

=IF(AND([%]@row = 1, OR(Type@row = "Project", Type@row = "Output")), "Blue", IF(AND(date@row < TODAY(), OR(Type@row = "Project", Type@row = "Output")), "Red", health@row))

KaitlinH

@AnalyticOwl This is what I have and it is saying #UNPARSEABLE

=IF(AND([Status %]@row = 1), OR(Category@row = "Project", Category@row = "Output"))"Blue", IF(AND([Expected Completion Date]@row < TODAY(), OR(Category@row = "Project", Category@row = "Output")), "Red", Health@row))

AnalyticOwl

@KaitlinH you have a closing parenthesis after the number 1 that should not be there also you are missing a comma after the double parenthesis before the word blue.

I highly recommend you practice start building the formulas from the basic structure and see each, block also SS when you are building the formula tells you in which area you are so you can have a better idea of where you are on the formula. Try to follow it!

also, the color-coding parenthesis helps a lot. Step by step you will be feeling more comfortable with the formula and you will be able to troubleshoot in the future.

Practice makes us better every day!

J