Multiple IF/AND formula

Jennifer Parins

I am trying to write a formula for the following:

If a percent is <= 0, "Major Gap"

If the percent is between 0 and 10, "Minor Gap"

If the percent is >=10, "No Gap"

=IF(AND([Percent Change]@row > 0, [Percent Change]@row < 10), "Minor Gap", IF([Percent Change]@row >= 10, "No Gap", IF([Percent Change]@row <= 0, "Major Gap")))

I am not receiving the correct option. Any help would be appreciated.

KPH

Hi Jennifer

Your formula looks good and works as expected. When you say you are not receiving the correct option, do you mean that you see "Major Gap" where you expect "Minor Gap" and vice versa?

This is the result I have:

which is exactly what you describe

• If a percent is less than or equal to 0, "Major Gap"
• If the percent is between 0 and 10, "Minor Gap"
• If the percent is greater than or equal to 10, "No Gap"

Maybe you have the less than and greater than symbols the wrong way around???

Do you want it to look like this?

If so that is

• If a percent is >= 10, "Major Gap"
• If the percent is between 0 and 10, "Minor Gap"
• If the percent is <=0, "No Gap"

Instead of

=IF(AND([Percent Change]@row > 0, [Percent Change]@row < 10), "Minor Gap", IF([Percent Change]@row >= 10, "No Gap", IF([Percent Change]@row <= 0, "Major Gap")))

Use

=IF(AND([Percent Change]@row > 0, [Percent Change]@row < 10), "Minor Gap", IF([Percent Change]@row <= 0, "No Gap", IF([Percent Change]@row >= 10, "Major Gap")))

I hope that is the answer. 🤞

If not, here is how I would build the formula to your original specification which might help you troubleshoot.

You might find it helpful to simplify the formula and remove the AND if you want to as the "Minor Gap" option could be covered by "everything that is not "Major Gap" or "No Gap".

I will build it slowly to show what I mean as it helps to troubleshoot.

Start with If a percent is <= 0, "Major Gap"

The formula is

=IF([Percent Change]@row <= 0, "Major Gap", "value if false")

That results in "Major Gap" when the IF is true and "value if false" where it is not. This part is a place holder. But you can check that the value when true is what you want. If it isn't right you have a small formula to adjust and can see the issue easily.

Now, make a formula for If the percent is >=10, "No Gap"

That formula is

=IF([Percent Change]@row >= 10, "No Gap", "value if false")

That results in "No Gap" when the IF is true and "value if false" where it is not. Again, you can see if this part works.

Now you can combine those two formula. Replacing the "value if false" in the first one with the second formula to be evaluated instead. Like this

=IF([Percent Change]@row <= 0, "Major Gap", IF([Percent Change]@row >= 10, "No Gap", "value if false"))

This results in "Major Gap" where the first statement is true. If that is false and the next statement is true it results in "No Gap" and if that is false it results in "value if false".

You can then replace "value if false" with "Minor Gap" as this covers all other situations.

=IF([Percent Change]@row <= 0, "Major Gap", IF([Percent Change]@row >= 10, "No Gap", "Minor Gap"))