Skip to main content

IF, AND, OR Formula Help

  • July 8, 2021
  • 1 reply
  • 18 views
M

Hi Everyone,

I have a field called “GL value” that is a number between 0 and about 100. I’d like to have a separate field that does the following:

If GL value is equal to or over 20 = :x: High GL
If GL value is between 11 and 19 = :orange_circle: Moderate GL
If GL value is 10 or less.= :white_check_mark: Low GL

Can someone help me write this? This is where I’ve got to:
IF(OR({GL Value}>=20), " :x: High GL”), IF(AND({GL Value}>=11, {GL Value}<=19), " :orange_circle: Moderate GL”), IF({GL Value}<=10, " :white_check_mark: Low GL”)

Thanks so much!

1 reply

Justin_Barrett
Forum|alt.badge.img+21

Welcome to the community, @Meera_Malhotra! :grinning_face_with_big_eyes: First, if you only have one condition to check, there’s no need to wrap the OR() function around it; e.g. your first condition checking for values >= 20.

Second, you’re currently closing the IF() function each time, which is invalid. The third argument of the IF() function is what should be executed if the first argument—the condition that you’re testing—is false. In other words, the basic structure for nesting three IF() functions should look like this:

IF(condition1, result_if_true, IF(condition2, result_if_true, IF(condition3, result_if_true, optional_result_if_false)))

Third, you can simplify the conditions that you’re testing. If the first test fails, you automatically know that the value isn’t >= 20. It must be 19 or lower, so the next test only needs to see if it’s >= 11. The same goes for your final test. If it failed the two tests before it, it’s automatically <= 10, so there’s no need to even test the value.

With all of that in mind, this is your new formula:

IF({GL Value} >= 20, "❌ High GL", IF({GL Value} >= 11, "🟠 Moderate GL", "✅ Low GL"))

The same thing spread across several lines for greater clarity:

IF(
    {GL Value} >= 20,
    "❌ High GL",
    IF(
        {GL Value} >= 11,
        "🟠 Moderate GL",
        "✅ Low GL"
    )
)
R
Terms & ConditionsAccessibility statement