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
Check out the Formula Handbook template!