Nested IF formula to return a value
I am trying to have one of four categories returned in a field field called Grouping (Values to return: Enterprise Project, NonEnterprise Project, TBD  In Discovery, Tech Annual Initiative) based on the value in another field (Estimated Block Hours).
Here is the criteria in the Estimated Block Hours field for when to select the different categories in the Grouping field:
Enterprise Project: >= 80
NonEnterprise Project: < 80
Tech Annual Initiative: TIA (could also have this be '0' if it needs to be a number)
TBD  In Discovery: Any other value or blank.
Here is my formula, but I can only get the Enterprise Project or NonEnterprise Project to return the correct values:
=IF([Estimated Block Hours]1 < 80, "NonEnterprise Project", IF([Estimated Block Hours]1 >= 80, "Enterprise Project", IF([Estimated Block Hours]1 = TIA, "Tech Annual Initiative", "TBD  In Discovery")))
Thanks!
Best Answer

Hey Kristi!
I think I got it figured out for you. First off, your formula needs quotes around "TIA" as it's a text value (see bolded below):
=IF([Estimated Block Hours]1 < 80, "NonEnterprise Project", IF([Estimated Block Hours]1 >= 80, "Enterprise Project", IF([Estimated Block Hours]1 = TIA, "Tech Annual Initiative", "TBD  In Discovery")))
I imagine you got that fixed because you said you are only getting "Enterprise Project" or "NonEnterprise Project" results. I believe its because of the order of your IF statements plus a blank value has a value of 0 (so any blank values are less than 80, which would make any blank value equal to "NonEnterprise Project".
You'll need something in your formula that says "If the Estimated Block Hours are less than 80, but greater than 0, then show "NonEnterprise Project". That's the third line below (with the AND statement).
Third, you should start with your text value first, since the formula will calculate each IF statement in order. In the mind of the formula "TIA" has a value of 0. So the "TIA" IF statement needs to come first so the formula looks for TIA first.
=IF([Estimated Block Hours]@row = "TIA", "Tech Annual Initiative", IF([Estimated Block Hours]@row >= 80, "Enterprise Project", IF(AND([Estimated Block Hours]@row < 80, [Estimated Block Hours]@row > 0), "NonEnterprise Project", "TBD  In Discovery")))
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
Answers

Hey Kristi!
I think I got it figured out for you. First off, your formula needs quotes around "TIA" as it's a text value (see bolded below):
=IF([Estimated Block Hours]1 < 80, "NonEnterprise Project", IF([Estimated Block Hours]1 >= 80, "Enterprise Project", IF([Estimated Block Hours]1 = TIA, "Tech Annual Initiative", "TBD  In Discovery")))
I imagine you got that fixed because you said you are only getting "Enterprise Project" or "NonEnterprise Project" results. I believe its because of the order of your IF statements plus a blank value has a value of 0 (so any blank values are less than 80, which would make any blank value equal to "NonEnterprise Project".
You'll need something in your formula that says "If the Estimated Block Hours are less than 80, but greater than 0, then show "NonEnterprise Project". That's the third line below (with the AND statement).
Third, you should start with your text value first, since the formula will calculate each IF statement in order. In the mind of the formula "TIA" has a value of 0. So the "TIA" IF statement needs to come first so the formula looks for TIA first.
=IF([Estimated Block Hours]@row = "TIA", "Tech Annual Initiative", IF([Estimated Block Hours]@row >= 80, "Enterprise Project", IF(AND([Estimated Block Hours]@row < 80, [Estimated Block Hours]@row > 0), "NonEnterprise Project", "TBD  In Discovery")))
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

This fixed formula works like a charm! Thank you so much! I appreciate the additional guidance you provided and quick response as well :)

@Kristi R Glad to help!
If this answer answers your question, please press "Yes" above  it helps the community (and those random Googlers out there 👀) find solutions like yours faster.
Love,
Brett Wyrick  Connect with me on LinkedIn.

2023 update: I'm no longer working on Smartsheet stuff. I started working at Microsoft in 2022, plus I have 1yearold twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!