IF Expression That Evaluates Various Numeric Ranges

[NickVa1]

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

[Richard_Le_ · Accepted solution]

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.

[NickVa1]

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

Cheers.

Nick