Nested if formula not working
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.
Best Answer

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.
Answers

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!