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
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!