Nested IF formula to return a value

Kristi R

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!

Brett Wyrick

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")))

Brett Wyrick

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")))

Kristi R

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

@Kristi R Glad to help!