Nested if formula not working

Christina C

Hello,

This has me stumped. This is a simple nesting if statement to return RYG symbol. I created the formula and the results were incorrect. I thought I was going crazy so I used the AI formula generater to see what formula it would use. It provided the SAME formula.

=IF([% Complete]@row < 70, "Red", IF(AND([% Complete]@row >= 70, [% Complete]@row < 100), "Yellow", "Green"))

I think I am losing my mind!

I think it should then be the following, but why did the AI generator give me the above?

=IF([% Complete]@row < .7, "Red", IF(AND([% Complete]@row >= .7, [% Complete]@row < 1), "Yellow", "Green"))

Thanks.

KPH

Hi @Christina C

Both formula are correct depending on how your data is formatted. In both the AND is superfluous as the second IF will only be evaluated if the first is false. In other words, the second IF is only considered in rows where % Complete is not less than .7 or 70. So you can skip the part that says it must be above or equal to .7 or 70 (if you want to).

The first formula you have, will work if the values in your % Complete column are numbers and not in a % formatted column. I have it in column 2 here:

As soon as you enter a % sign after a number the format of the column changes to a % column. You can also make this change by clicking on the % icon in the toolbar. In a % format column 100% is 1 not 100. In that situation the second formula you had is the one to use.

I have it in column 4 here:

If you use the first formula on a column that is % formatted then everything less than 7000% will have a red symbol.

I hope that explains the difference.

KPH

Hi @Christina C

Both formula are correct depending on how your data is formatted. In both the AND is superfluous as the second IF will only be evaluated if the first is false. In other words, the second IF is only considered in rows where % Complete is not less than .7 or 70. So you can skip the part that says it must be above or equal to .7 or 70 (if you want to).

The first formula you have, will work if the values in your % Complete column are numbers and not in a % formatted column. I have it in column 2 here:

As soon as you enter a % sign after a number the format of the column changes to a % column. You can also make this change by clicking on the % icon in the toolbar. In a % format column 100% is 1 not 100. In that situation the second formula you had is the one to use.

I have it in column 4 here:

If you use the first formula on a column that is % formatted then everything less than 7000% will have a red symbol.

I hope that explains the difference.