Using IF and AND

[Rhonda_Johnson]

I’ve seen a few things that are similar but I’m still not getting my formula to work and I’ve tried a lot of different iterations of the formula with no luck.
What I’m trying to do
Fields: Pos Chg, 6 Mo Pos
If Pos Chg < 0 AND 6 Mo Pos < 10 then I want to say “TOP”
If Pos Chg > 0 AND 6 Mo Pos < 10 then I want to say “Good”
Otherwise blank

IF (AND ( {POS chg from Pre-Post} < 0, {POS 6 MO POST} < 10), “TOP”, IF ( {POS 6 MO POST} < 10, “Good”, “”)

Any help is greatly appreciated.

[Kamille_Parks]

Proper syntax:

IF(
   AND({Pos Chg} < 0, {6 Mo Pos} < 10),
   "TOP",
   IF(
      AND({Pos Chg} > 0, {6 Mo Pos} < 10),
      "Good"
   )
)

Since you’re checking if {POS 6 MO POST} < 10 in both cases, you can also try only declaring that once.

IF(
   {POS 6 MO POST} < 10,
   IF(
      {Pos Chg} < 0,
      "TOP",
      "GOOD"
   )
)

[Rhonda_Johnson]

I’ve tried both of these and still get an error when I try to save. I’ve copied / pasted. I’ve retyped. I’ve checked field names and syntax. Honestly, I can’t figure it out

[Kamille_Parks]

Post a screenshot of your error.

[augmented]

I think the clue is in “I’ve copied/pasted”. Double quote woes.

[kuovonne]

Kamille_Parks:

IF (

There’s an extra space after the IF. Delete that space so that the open parenthesis is right next to the IF. The field names in the original post also are not clear–they have different names depending on which part of the post you read.

[Alexey_Gusev]

Hi,
it’s not clear what are your field names in real
{POS chg from Pre-Post}, {POS 6 MO POST} or
{Pos Chg},{6 Mo Pos}
formula will not work if you have mistake in field name

second, you have to ensure that you compare numbers, not strings looks like numbers (strings align to the left side of a cell, numbers - to the right)
otherwise, you have to change fields type, or use value(x)<10 instead of (x<10). you should enable “allow negative” in first field, in order to get “TOP” :slightly_smiling_face:

finally, your field names are extremely inconvenient to write a formula. if that became trouble, i usually rename it to something very simple, write formula, ensure it works, rename fields again. Airtable will take care for update a formula.

for multiple choises i usually use another operator, easier to edit values,
but ‘less readable’, although looks good for several expressions applying binary logic.
here is an example, to see difference between string and number.
hope it will help you

[Rhonda_Johnson]

Thank you ALL! I was able to get this to work. I tried using SWITCH and couldn’t get it to work, but I’m going to play with that as I think it might prove very useful in some other things I’m doing. I think I will be able to simplify this formula over time, but it is working for now, so I’m moving on to the next step.