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, Non-Enterprise 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

Non-Enterprise 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 Non-Enterprise Project to return the correct values:


=IF([Estimated Block Hours]1 < 80, "Non-Enterprise Project", IF([Estimated Block Hours]1 >= 80, "Enterprise Project", IF([Estimated Block Hours]1 = TIA, "Tech Annual Initiative", "TBD - In Discovery")))


Thanks!

Best Answer

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    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, "Non-Enterprise 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 "Non-Enterprise 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 "Non-Enterprise 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 "Non-Enterprise 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), "Non-Enterprise 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 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Answers

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭
    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, "Non-Enterprise 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 "Non-Enterprise 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 "Non-Enterprise 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 "Non-Enterprise 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), "Non-Enterprise 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 1-year-old 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 :)

  • Brett Wyrick
    Brett Wyrick ✭✭✭✭✭

    @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 1-year-old twins at home and frankly, I don't have enough time to do Smartsheet anymore. It's been real, Smartsheeters!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!