IF Expression That Evaluates Various Numeric Ranges | Community
Skip to main content
N
NickVa7
Level 9
November 2, 2020
Solved

IF Expression That Evaluates Various Numeric Ranges

  • November 2, 2020
  • 2 replies
  • 554 views

Hi WF Community,

Looking for some help with a calculated custom field, and specifically how to write an IF expression when you want to evaluate multiple numeric ranges:

If Field A <= 99,999 then score 1

If Field A is between 100,000 and 299,999 then score 3

If Field A is >= 300,000 then score 5

I think it's mostly the middle (2nd) portion I'm uncertain how to write (between 2 values). Here's what I have so far...

IF(Field A<=99999,1,IF(Field A ????,3, IF(Field A>299999,5,””)))

Thanks.

Nick

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Richard_Le_

    Hi Nick,

    Workfront will go through your IF statement sequentially and will stop when a condition is met. So following your example:

    IF(Field A<=99999,1,IF(Field A<=299999,3,IF(Field A>299999,5,””)))

    If Field A had a value of 500, it will return true on the first condition (<=99999) and output '1'. It would not go onto the the next condition and return '3' as well (even though 500 is less than 299999) because the first condition had already been met. For this reason, you wouldn't need to write a statement that looks between two values, instead a simple less than or equal to condition will suffice for the middle portion.

    However, if you did have a scenario where you needed to fulfil two conditions as part of the same expression you could use the '&&' Operator. Continuing with your example, the below statement would deliver the same outcome as what I have described above.

    IF(Field A<=99999,1,IF(Field A>99999 && Field A<=299999,3,IF(Field A>299999,5,””)))

    Hope that helps!

    Best Regards,

    Rich.

    2 replies

    R
    Community Advisor
    Richard_Le_Community AdvisorAccepted solution
    Community Advisor
    November 2, 2020

    Hi Nick,

    Workfront will go through your IF statement sequentially and will stop when a condition is met. So following your example:

    IF(Field A<=99999,1,IF(Field A<=299999,3,IF(Field A>299999,5,””)))

    If Field A had a value of 500, it will return true on the first condition (<=99999) and output '1'. It would not go onto the the next condition and return '3' as well (even though 500 is less than 299999) because the first condition had already been met. For this reason, you wouldn't need to write a statement that looks between two values, instead a simple less than or equal to condition will suffice for the middle portion.

    However, if you did have a scenario where you needed to fulfil two conditions as part of the same expression you could use the '&&' Operator. Continuing with your example, the below statement would deliver the same outcome as what I have described above.

    IF(Field A<=99999,1,IF(Field A>99999 && Field A<=299999,3,IF(Field A>299999,5,””)))

    Hope that helps!

    Best Regards,

    Rich.

      N
      NickVa7Author
      Level 9
      November 9, 2020

      This is great, thanks for explaining, Rich. I'll try both options.

      Cheers.

      Nick

      Terms & ConditionsAccessibility statement

      Loading footer...