IF AND

Pamela Wagner

Having trouble with a formula for RYG status. I need a statement for criteria: if its greater than 60% but less than 100%, turn the ball to Yellow.

I have this: =IF(AND([% Complete]3 > 0.6, [% Complete]3 < 1, "Yellow"))

But I keep getting either Incorrect Argument Set or Invalid Data Type.

Why??!

Malaina Hudson

Try this and see if it works. It looks like you're not closing the AND function before moving into the If True section of the IF function.

=IF(AND([% Complete]@row > 0.6, [% Complete]@row < 1), "Yellow")

Pamela Wagner

Thanks Malania! I got it to work as a single string but now can't get it to work in the full string: =IF([% Complete]@row < 0.6, "Red", IF(AND([% Complete]@row > 0.6, [% Complete]@row < 1), "Yellow"), IF([% Complete]@row = 1, “Green”))

I always have issues with parens and need an easy way to remember how to get them right. I'm counting all of the open parens and match them with closing but it's still not working.

Pamela Wagner

I asked a colleague and it looks like we got it to work:

=IF([% Complete]5 <= 0.6, "Red", IF([% Complete]5 < 1, "Yellow", IF([% Complete]5 = 1, "Green")))

I think in the full string I don't need the AND statement since it's already indicated in the Red category?? This may have happened to me with an earlier formula as well. Thoughts?

Ed Close

Thank you very much for the prompt reply. It's almost working as expected. My formula is now as follows, =IF([Adjusted Score]2 < 11, "Tier 3", IF(OR([Adjusted Score]2 >= 12, [Adjusted Score]2 <= 20), "Tier 2", IF([Adjusted Score]2 > 21, "TIER 1")))

The problem I am having is I have a project with an adjusted score of 27 but it's showing up as a tier 2 when I need it to be Tier 1. It's as if the formula is stopping at the 2nd operation, the If(Or portion and since this particular record is 12, it's coding it as tier 2. Any ideas?

Pamela Wagner

@Ed Close I think you posted in the wrong discussion!