If Formula
I'm trying to add and additional item to this IF Statement, when the total column is exceeding 150 and the Total Percent Used Column is Blank. This is my existing IF Statement
=IF([Total Percent Used]@row < 0.5, "", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF([Total Percent Used]@row > 0.75, "Notify", IF([Total Percent Used]@row < 100, "Exceeds"))))
I'm trying to add the following: IF(AND([Total Percent Used]@row = 0, [Total]@row >150000, "Review")
The second formula is not working when added or returns a blank. What am I missing
Best Answer
-
Thank you for your help, this was my final formula
=IF(AND([Total Percent Used]@row < 0.01, Total@row > 150000), "Review", IF([Total Percent Used]@row < 0.01, "", IF(AND([Total Percent Used]@row > 0.02, [Total Percent Used]@row <= 0.5), "In Range", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF(AND([Total Percent Used]@row >= 0.75, [Total Percent Used]@row < 1), "Notify", IF([Total Percent Used]@row >= 1, "Exceeds"))))))
Answers
-
Hi @TracyS — the percent used column seems to be empty in most cases. You are looking for the specific value of "0", which does not occur in the screenshot. Try this instead:
=IF(AND([Total Percent Used]@row = "", [Total]@row >150000, "Review")
-
Hey @TracyS,
Since you have a formula already checking if the % is less than 50, it'll trigger that true statement before it'll trigger the one you're trying to add (assuming you're adding it at the end of the chain of IFs. I'd put the new IF statement as the first IF in the larger formula first.
Secondly, it appears you're missing a closing parenthesis around the AND portion of your statement:
IF(AND([Total Percent Used]@row = 0, [Total]@row >150000), "Review")
So as a whole, the formula should work if it's entered like this:
=IF(AND([Total Percent Used]@row = 0, [Total]@row >150000), "Review", IF([Total Percent Used]@row < 0.5, "", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF([Total Percent Used]@row > 0.75, "Notify", IF([Total Percent Used]@row < 100, "Exceeds")))))
Hope this helps!
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
If you do sometimes see the value 0 and also want to include that criteria, then update it to:
=IF(AND(OR([Total Percent Used]@row = 0, [Total Percent Used]@row = ""), [Total]@row >150000, "Review")
-
Ok, so nothing returned for the review on the blue line. I added the formula you provided:
=IF(AND([Total Percent Used]@row = 0, [Total]@row >150000), "Review", IF([Total Percent Used]@row < 0.5, "", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF([Total Percent Used]@row > 0.75, "Notify", IF([Total Percent Used]@row < 100, "Exceeds"))))) -
Thank you for your help, this was my final formula
=IF(AND([Total Percent Used]@row < 0.01, Total@row > 150000), "Review", IF([Total Percent Used]@row < 0.01, "", IF(AND([Total Percent Used]@row > 0.02, [Total Percent Used]@row <= 0.5), "In Range", IF(AND([Total Percent Used]@row >= 0.5, [Total Percent Used]@row <= 0.75), "Approaching", IF(AND([Total Percent Used]@row >= 0.75, [Total Percent Used]@row < 1), "Notify", IF([Total Percent Used]@row >= 1, "Exceeds"))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!