Calculated Field to Convert Project Planned Hours to "Tier" (or Effort Level)

[Angie_S]

I'm trying to write a calculated text field that will make it so that we no loner have to manually select a "Tier" for each of our projects on their custom forms.

Use Case: Current-state, after a request is converted to a project and the project is built out, the project manager looks at the total planned hours for the project and then selects the correct corresponding "Tier" in a custom form. We'd like to make a calculated field that automatically picks the correct tier instead of the project manager needing to do it (and possibly selecting the wrong tier, which goes into our reports and is something we have to fix fairly regularly).

Example: Tier 1 projects are >=100 project planned hours, Tier 2 are <= 50 project planned hours hours, Tier 3 projects are <=25 project planned hours

Every code I have written has failed to accurately convert (project) planned hours to a tier. The code below will assign a 45.5 planned hour project to "Tier 1" even though it should be Tier 2 ( because 45.5 is lesser than or equal to 50). I'm sorry to say I've spent hours trying to figure this out - any thoughts on what I am doing wrong?

IF({workRequired}<=25,"Tier 3",IF({workRequired}<=50,"Tier 2",IF({workRequired}<=100,"Tier 1",IF({workRequired}>=101,"Tier 1","Tier 1"))))

[skyehansen]

without actually addressing your calculation, have you considered putting a test field on your form, to just see what "workRequired" is? I put one on my form, and 3 hours is coming out as "180" so I'm guessing you're failing to account for a conversion from minutes to hours, for one thing. I mean, your calculation may be problematic in other ways as well, but I would start with planning out some good troubleshooting measures, and at the very least, multiply all the numbers in your current equation by 60.

[Angie_S · Accepted solution]

Thanks @skyehansen , I think that's the nudge that did the trick! I re-wrote my code as follows:

IF(DIV({workRequired},60)<=25,"Tier 3",IF(DIV({workRequired},60)<=50,"Tier 2",IF(DIV({workRequired},60)<=100,"Tier 1",IF(DIV({workRequired},60)>=101,"Tier 1","Tier 1"))))

And now each project that the custom form is applied to accurately reports the tier. Thanks for helping this noob!